Change the color of a range of cells.

[Srajes]

Hi,

I need to change the color of a range of cells depending up on the value in
another cell.
Consider cells
1 2 3 4 5
, I need to change the color of all thse 5 cells depending up on the value
in cell 5.

Thanks in advance for your help.
SR

 

[Srajes]

Sure, I will take care in future.

Pls post in ONLY ONE GROUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Patrick Molloy]

you can do this easily with Conditional Formatting

or in code, something akin to this:-
If Range("A5")=somevalue then range("A1:A5").Interior.Colorindex =
somecolorcode
where you put appropriate values in for somevalue and somecolorcode

 

[Srajes]

Conditinal formatting allows me to check only 3 conditions. I have 7
conditions. I am using Excel 2003.

 

[Gord Dibben]

Modify to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Range("A11")
If Range("E1") = "" Then Exit Sub
'Determine the color
Select Case Range("E1").Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
For Each rng In vRngInput
rng.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben MS Excel MVP

 

[Don Guillett]

Gord. OP using a drop down so, in the OTHER post, I suggested this vlookup
instead.

I found your drop down list and named the range colorpicks. I also named the
validation range to colorpicksA so you may now move it as I did , even to
another sheet, and it will still work because it is assigned to the named
range. Goto the table and change the color numbers as desired. I have also
attached colorpalette so you can see the colors. You had validation assigned
to the whole column. Do NOT do that. Now when you click on col M (if
validation there) the color will change according to the vlookup table.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Columns("m").Column Then Exit Sub
Cells(Target.Row, 1).Resize(, 13).Interior.ColorIndex = _
Application.VLookup(Target, Range("colorpicks"), 2, 0)
End Sub

 

[Gord Dibben]

Thanks Don

Missed OTHER post, I guess.


Gord