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

Sure, I will take care in future.

"Don Guillett" wrote:

Pls post in ONLY ONE GROUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

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

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

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

Thanks Don

Missed OTHER post, I guess.


Gord

On Thu, 25 Jun 2009 07:13:18 -0500, "Don Guillett"
wrote:

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