Shading cells based on a value in another cell

**lucky_girl** · 02-26-2009, 04:44 PM

New to forum - I would like to shade a range of cells based on a value entered in another cell. For example if I enter "5", I would like to shade the next 5 cells in a row. I have tried conditional formatting, but have not been successful. Thank you in advance for your help!

**Ironman** · 02-26-2009, 05:43 PM

Hi

I've just did something similar but with bold and indents. I would assume that you could do the same by using offset(0,1)....here is a portion of my code (this looks in A2 and compares that value to H2)..if they are the same then it will indent the value in C2.

Dim IDnum As Range
    For Each IDnum In Range("a:a")
    If IDnum.Value = IDnum.Offset(0, 7) Then
    IDnum.Offset(0, 2).IndentLevel = 1
    IDnum.Offset(0, 2).Font.FontStyle = "Bold"
    IDnum.Offset(0, 2).Font.Underline = xlUnderlineStyleSingle      
   End if
next

Hope that helps

**Ironman** · 02-26-2009, 06:29 PM

Hey L_girl - I took another look at put this quick VBA code together, I think this is a little more complete of an example. I'm not as good of a VBA'r as many of the folks on this forum, so if anyone has other suggestions for improving this code then by all means...I'm sure there is a much more effective way to do this but at the very least this should get you started....

Sub ColorIT()

Dim IDnum As Range
'
' Color 6 is Yellow
'
For Each IDnum In Range("a:a")
If IDnum.Value = 1 Then
IDnum.Offset(0, 1).Interior.ColorIndex = 6
ElseIf IDnum.Value = 2 Then
IDnum.Offset(0, 1).Interior.ColorIndex = 6
IDnum.Offset(0, 2).Interior.ColorIndex = 6

ElseIf IDnum.Value = 3 Then
IDnum.Offset(0, 1).Interior.ColorIndex = 6
IDnum.Offset(0, 2).Interior.ColorIndex = 6
IDnum.Offset(0, 3).Interior.ColorIndex = 6


ElseIf IDnum.Value = 4 Then
IDnum.Offset(0, 1).Interior.ColorIndex = 6
IDnum.Offset(0, 2).Interior.ColorIndex = 6
IDnum.Offset(0, 3).Interior.ColorIndex = 6
IDnum.Offset(0, 4).Interior.ColorIndex = 6

ElseIf IDnum.Value = 5 Then
IDnum.Offset(0, 1).Interior.ColorIndex = 6
IDnum.Offset(0, 2).Interior.ColorIndex = 6
IDnum.Offset(0, 3).Interior.ColorIndex = 6
IDnum.Offset(0, 4).Interior.ColorIndex = 6
IDnum.Offset(0, 5).Interior.ColorIndex = 6

End If

Next
End Sub

Have a good day...

**VBA Noob** · 02-26-2009, 06:46 PM

Ironman,

Maybe

Dim cl As Range
For Each cl In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    If IsNumeric(cl.Value) Then
        cl.Offset(0, 1).Resize(1, cl.Value).Interior.ColorIndex = 6
    End If
Next cl

VBA Noob

**Ironman** · 02-26-2009, 06:54 PM

Short and sweet..nice

Tell me, what does

Resize(1, cl.Value).

Do...?

**VBA Noob** · 02-26-2009, 06:59 PM

As the cell will have a value I use the resize method to resize the range to that number of cells. Search excel help or take a look at the below for more

http://msdn.microsoft.com/en-us/library/aa139976.aspx

VBA Noob