[SOLVED] Cell color based upon cell value

[My View]

I know you can use Conditional Formatting to allocate a colour for a cell
value but you are limited to 4 alternatives (ie the default cell color and
only 3 variations in Conditional Formatting).

How can I allocate a different color for 5 different cell values?

For example:
If cell value = 1 then cell color is green
If cell value = 2 then cell color is blue
If cell value = 3 then cell color is yellow
If cell value = 4 then cell color is red
If cell value = 5 then cell color is magenta

regards

PeterH

[Microsoft]

Hi Peter,

You can do that with macros. If you want to do it with macros just let me
know. Otherwise you could try recording a macro first, and modifying the
generated code.

Shafiee.

"My View" <reply to newsproup@NOSPAM.net> wrote in message
news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
>I know you can use Conditional Formatting to allocate a colour for a cell
> value but you are limited to 4 alternatives (ie the default cell color and
> only 3 variations in Conditional Formatting).
>
> How can I allocate a different color for 5 different cell values?
>
> For example:
> If cell value = 1 then cell color is green
> If cell value = 2 then cell color is blue
> If cell value = 3 then cell color is yellow
> If cell value = 4 then cell color is red
> If cell value = 5 then cell color is magenta
>
> regards
>
> PeterH
>
>

[ScottO]

Does it have to be cell colour, or can you accept just setting different font colours?
Rgds,
ScottO

"My View" <reply to newsproup@NOSPAM.net> wrote in message
news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
| I know you can use Conditional Formatting to allocate a colour for a cell
| value but you are limited to 4 alternatives (ie the default cell color and
| only 3 variations in Conditional Formatting).
|
| How can I allocate a different color for 5 different cell values?
|
| For example:
| If cell value = 1 then cell color is green
| If cell value = 2 then cell color is blue
| If cell value = 3 then cell color is yellow
| If cell value = 4 then cell color is red
| If cell value = 5 then cell color is magenta
|
| regards
|
| PeterH
|
|

[My View]

Preferably cell colors as they are more obvious when looking at a large
spreadsheet.


"ScottO" <scott_orchard_REMOVETHIS@hotmail.comTHISTOO> wrote in message
news:OyEXJNHgFHA.3540@TK2MSFTNGP14.phx.gbl...
> Does it have to be cell colour, or can you accept just setting different
font colours?
> Rgds,
> ScottO
>
> "My View" <reply to newsproup@NOSPAM.net> wrote in message
> news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
> | I know you can use Conditional Formatting to allocate a colour for a
cell
> | value but you are limited to 4 alternatives (ie the default cell color
and
> | only 3 variations in Conditional Formatting).
> |
> | How can I allocate a different color for 5 different cell values?
> |
> | For example:
> | If cell value = 1 then cell color is green
> | If cell value = 2 then cell color is blue
> | If cell value = 3 then cell color is yellow
> | If cell value = 4 then cell color is red
> | If cell value = 5 then cell color is magenta
> |
> | regards
> |
> | PeterH
> |
> |
>
>

[My View]

A macro would be fine. Whatever is easiest.


"Microsoft" <mohamed.shafee@gmail.com> wrote in message
news:OczR6MHgFHA.3940@tk2msftngp13.phx.gbl...
> Hi Peter,
>
> You can do that with macros. If you want to do it with macros just let me
> know. Otherwise you could try recording a macro first, and modifying the
> generated code.
>
> Shafiee.
>
> "My View" <reply to newsproup@NOSPAM.net> wrote in message
> news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
> >I know you can use Conditional Formatting to allocate a colour for a cell
> > value but you are limited to 4 alternatives (ie the default cell color
and
> > only 3 variations in Conditional Formatting).
> >
> > How can I allocate a different color for 5 different cell values?
> >
> > For example:
> > If cell value = 1 then cell color is green
> > If cell value = 2 then cell color is blue
> > If cell value = 3 then cell color is yellow
> > If cell value = 4 then cell color is red
> > If cell value = 5 then cell color is magenta
> >
> > regards
> >
> > PeterH
> >
> >
>
>

[Bob Phillips]

See http://www.xldynamic.com/source/xld.....Download.html

--
HTH

Bob Phillips

"My View" <reply to newsproup@NOSPAM.net> wrote in message
news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
> I know you can use Conditional Formatting to allocate a colour for a cell
> value but you are limited to 4 alternatives (ie the default cell color and
> only 3 variations in Conditional Formatting).
>
> How can I allocate a different color for 5 different cell values?
>
> For example:
> If cell value = 1 then cell color is green
> If cell value = 2 then cell color is blue
> If cell value = 3 then cell color is yellow
> If cell value = 4 then cell color is red
> If cell value = 5 then cell color is magenta
>
> regards
>
> PeterH
>
>

[My View]

Does this have to be installed on all PCs that open the spreadsheet?


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:eMGZv8HgFHA.2880@TK2MSFTNGP14.phx.gbl...
> See http://www.xldynamic.com/source/xld.....Download.html
>
> --
> HTH
>
> Bob Phillips
>
> "My View" <reply to newsproup@NOSPAM.net> wrote in message
> news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
> > I know you can use Conditional Formatting to allocate a colour for a
cell
> > value but you are limited to 4 alternatives (ie the default cell color
and
> > only 3 variations in Conditional Formatting).
> >
> > How can I allocate a different color for 5 different cell values?
> >
> > For example:
> > If cell value = 1 then cell color is green
> > If cell value = 2 then cell color is blue
> > If cell value = 3 then cell color is yellow
> > If cell value = 4 then cell color is red
> > If cell value = 5 then cell color is magenta
> >
> > regards
> >
> > PeterH
> >
> >
>
>

[Bob Phillips]

Yep!

--
HTH

Bob Phillips

"My View" <reply to newsproup@NOSPAM.net> wrote in message
news:iu8ye.14101$oJ.4371@news-server.bigpond.net.au...
> Does this have to be installed on all PCs that open the spreadsheet?
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:eMGZv8HgFHA.2880@TK2MSFTNGP14.phx.gbl...
> > See http://www.xldynamic.com/source/xld.....Download.html
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "My View" <reply to newsproup@NOSPAM.net> wrote in message
> > news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
> > > I know you can use Conditional Formatting to allocate a colour for a
> cell
> > > value but you are limited to 4 alternatives (ie the default cell color
> and
> > > only 3 variations in Conditional Formatting).
> > >
> > > How can I allocate a different color for 5 different cell values?
> > >
> > > For example:
> > > If cell value = 1 then cell color is green
> > > If cell value = 2 then cell color is blue
> > > If cell value = 3 then cell color is yellow
> > > If cell value = 4 then cell color is red
> > > If cell value = 5 then cell color is magenta
> > >
> > > regards
> > >
> > > PeterH
> > >
> > >
> >
> >
>
>

[Gord Dibben]

View

Sheet event code can do the trick.....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 10 'green
Case Is = 2: Num = 5 'blue
Case Is = 3: Num = 6 'yellow
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 7 'magenta
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub


Gord Dibben Excel MVP

On Mon, 04 Jul 2005 08:58:39 GMT, "My View" <reply to newsproup@NOSPAM.net>
wrote:

>A macro would be fine. Whatever is easiest.
>
>
>"Microsoft" <mohamed.shafee@gmail.com> wrote in message
>news:OczR6MHgFHA.3940@tk2msftngp13.phx.gbl...
>> Hi Peter,
>>
>> You can do that with macros. If you want to do it with macros just let me
>> know. Otherwise you could try recording a macro first, and modifying the
>> generated code.
>>
>> Shafiee.
>>
>> "My View" <reply to newsproup@NOSPAM.net> wrote in message
>> news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
>> >I know you can use Conditional Formatting to allocate a colour for a cell
>> > value but you are limited to 4 alternatives (ie the default cell color
>and
>> > only 3 variations in Conditional Formatting).
>> >
>> > How can I allocate a different color for 5 different cell values?
>> >
>> > For example:
>> > If cell value = 1 then cell color is green
>> > If cell value = 2 then cell color is blue
>> > If cell value = 3 then cell color is yellow
>> > If cell value = 4 then cell color is red
>> > If cell value = 5 then cell color is magenta
>> >
>> > regards
>> >
>> > PeterH
>> >
>> >
>>
>>
>

[My View]

Thanks Gordon
For a person who has very little contact with Excel macros can you tell me
how to set-up the macro.
Also I only want the macro to work on certain cell ranges eg cells B4:B20
and D4:D20 and F4:F20 etc. How will this happen with a macro?
regards
PeterH


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:jj3jc1h4u0goj2gd7j092g1l1brmuqhs84@4ax.com...
> View
>
> Sheet event code can do the trick.....
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim Num As Long
> Dim rng As Range
> Dim vRngInput As Variant
> Set vRngInput = Intersect(Target, Range("A:A"))
> If vRngInput Is Nothing Then Exit Sub
> On Error GoTo endit
> Application.EnableEvents = False
> For Each rng In vRngInput
> 'Determine the color
> Select Case rng.Value
> Case Is = 1: Num = 10 'green
> Case Is = 2: Num = 5 'blue
> Case Is = 3: Num = 6 'yellow
> Case Is = 4: Num = 3 'red
> Case Is = 5: Num = 7 'magenta
> End Select
> 'Apply the color
> rng.Interior.ColorIndex = Num
> Next rng
> endit:
> Application.EnableEvents = True
> End Sub
>
>
> Gord Dibben Excel MVP
>
> On Mon, 04 Jul 2005 08:58:39 GMT, "My View" <reply to
newsproup@NOSPAM.net>
> wrote:
>
> >A macro would be fine. Whatever is easiest.
> >
> >
> >"Microsoft" <mohamed.shafee@gmail.com> wrote in message
> >news:OczR6MHgFHA.3940@tk2msftngp13.phx.gbl...
> >> Hi Peter,
> >>
> >> You can do that with macros. If you want to do it with macros just let
me
> >> know. Otherwise you could try recording a macro first, and modifying
the
> >> generated code.
> >>
> >> Shafiee.
> >>
> >> "My View" <reply to newsproup@NOSPAM.net> wrote in message
> >> news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
> >> >I know you can use Conditional Formatting to allocate a colour for a
cell
> >> > value but you are limited to 4 alternatives (ie the default cell
color
> >and
> >> > only 3 variations in Conditional Formatting).
> >> >
> >> > How can I allocate a different color for 5 different cell values?
> >> >
> >> > For example:
> >> > If cell value = 1 then cell color is green
> >> > If cell value = 2 then cell color is blue
> >> > If cell value = 3 then cell color is yellow
> >> > If cell value = 4 then cell color is red
> >> > If cell value = 5 then cell color is magenta
> >> >
> >> > regards
> >> >
> >> > PeterH
> >> >
> >> >
> >>
> >>
> >
>

[Gord Dibben]

Peter

Amended code to cover the three ranges specified. The "etc." you can add.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("B4:B20, D4:D20, F4:F20"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 10 'green
Case Is = 2: Num = 5 'blue
Case Is = 3: Num = 6 'yellow
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 7 'magenta
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

This is worksheet event code.

Right-click on the sheet tab and "View Code".

Copy/paste the code in there. Runs whenever a change is made to one of the
cells in the ranges.


Gord

On Tue, 05 Jul 2005 12:02:41 GMT, "My View" <reply to newsproup@NOSPAM.net>
wrote:

>Thanks Gordon
>For a person who has very little contact with Excel macros can you tell me
>how to set-up the macro.
>Also I only want the macro to work on certain cell ranges eg cells B4:B20
>and D4:D20 and F4:F20 etc. How will this happen with a macro?
>regards
>PeterH
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:jj3jc1h4u0goj2gd7j092g1l1brmuqhs84@4ax.com...
>> View
>>
>> Sheet event code can do the trick.....
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Dim Num As Long
>> Dim rng As Range
>> Dim vRngInput As Variant
>> Set vRngInput = Intersect(Target, Range("A:A"))
>> If vRngInput Is Nothing Then Exit Sub
>> On Error GoTo endit
>> Application.EnableEvents = False
>> For Each rng In vRngInput
>> 'Determine the color
>> Select Case rng.Value
>> Case Is = 1: Num = 10 'green
>> Case Is = 2: Num = 5 'blue
>> Case Is = 3: Num = 6 'yellow
>> Case Is = 4: Num = 3 'red
>> Case Is = 5: Num = 7 'magenta
>> End Select
>> 'Apply the color
>> rng.Interior.ColorIndex = Num
>> Next rng
>> endit:
>> Application.EnableEvents = True
>> End Sub
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Mon, 04 Jul 2005 08:58:39 GMT, "My View" <reply to
>newsproup@NOSPAM.net>
>> wrote:
>>
>> >A macro would be fine. Whatever is easiest.
>> >
>> >
>> >"Microsoft" <mohamed.shafee@gmail.com> wrote in message
>> >news:OczR6MHgFHA.3940@tk2msftngp13.phx.gbl...
>> >> Hi Peter,
>> >>
>> >> You can do that with macros. If you want to do it with macros just let
>me
>> >> know. Otherwise you could try recording a macro first, and modifying
>the
>> >> generated code.
>> >>
>> >> Shafiee.
>> >>
>> >> "My View" <reply to newsproup@NOSPAM.net> wrote in message
>> >> news:zq6ye.13958$oJ.6440@news-server.bigpond.net.au...
>> >> >I know you can use Conditional Formatting to allocate a colour for a
>cell
>> >> > value but you are limited to 4 alternatives (ie the default cell
>color
>> >and
>> >> > only 3 variations in Conditional Formatting).
>> >> >
>> >> > How can I allocate a different color for 5 different cell values?
>> >> >
>> >> > For example:
>> >> > If cell value = 1 then cell color is green
>> >> > If cell value = 2 then cell color is blue
>> >> > If cell value = 3 then cell color is yellow
>> >> > If cell value = 4 then cell color is red
>> >> > If cell value = 5 then cell color is magenta
>> >> >
>> >> > regards
>> >> >
>> >> > PeterH
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>

[pshardie@oznetcom.com.au]

Gord
Thanks - that works perfectly.
regards
PeterH