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
> (E-Mail Removed)
> "Srajes" <(E-Mail Removed)> wrote in message
> news0925857-68D0-4310-9D91-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news0925857-68D0-4310-9D91-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news0925857-68D0-4310-9D91-(E-Mail Removed)...
> > 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("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

On Wed, 24 Jun 2009 11:46:01 -0700, Srajes
<(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>> news0925857-68D0-4310-9D91-(E-Mail Removed)...
>> > 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
(E-Mail Removed)
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> 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
>
> On Wed, 24 Jun 2009 11:46:01 -0700, Srajes
> <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>>> news0925857-68D0-4310-9D91-(E-Mail Removed)...
>>> > 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"
<(E-Mail Removed)> 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