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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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("A1:D1")
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
On Wed, 24 Jun 2009 11:46:01 -0700, Srajes
wrote:
Conditinal formatting allows me to check only 3 conditions. I have 7
conditions. I am using Excel 2003.
"Patrick Molloy" wrote:
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" wrote in message
...
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