Cell Background Color Change according to numerical value

Conditional formatting will allow upto 3 conditions. I have five. How can you write an IF() statement/s to allow numerical values to change cell background color. 1-green, 2-blue, 3-yellow up to 5. Also how will you nest multiple IF's, the help files, aren't helping.
-Joe

You can use conditional format with cell value between 3 and 5

you can only add three conditions, after the third the 'add' button becomes greyed.

Yes conditional format has limitation of 3 conditions, you can use macro for this.

I am codding the macro for conditional format for cell a2 but it can be applied to any number cells.

you have to paste the below code, by right click sheet tab->view code and past the below code.

test it by enter values 1,2,3,4,5,6 in cell a2

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Address = "$A$2") Then
Range("a2").Select
temp = Selection.Value
If (temp = 1) Then
Selection.Interior.ColorIndex = 4
ElseIf (temp = 2) Then
Selection.Interior.ColorIndex = 33
ElseIf (temp = 3) Then
Selection.Interior.ColorIndex = 36
ElseIf (temp = 4) Then
Selection.Interior.ColorIndex = 48
ElseIf (temp = 5) Then
Selection.Interior.ColorIndex = 38
ElseIf (temp = 6) Then
Selection.Interior.ColorIndex = 30
End If
End If

End Sub

wow, thanks! I won't be able to try that until next week but thank you. you a life saver.
-Joe

Worksheet functions can't change cell background colors (or font colors,
borders, etc.).

If you need more than 3 conditions, you'll have to use VBA. If you
search the archives:

http://groups.google.com/advanced_gr...ugroup=*excel*

you'll find myriad ways of accomplishing this, depending on your
conditions.


In article <[email protected]>,
jrd269 <[email protected]> wrote:

>
> Conditional formatting will allow upto 3 conditions. I have five. How
> can you write an IF() statement/s to allow numerical values to change
> cell background color. 1-green, 2-blue, 3-yellow up to 5. Also how will
> you nest multiple IF's, the help files, aren't helping.

from what the groups say, it looks like it cannot be done. which really stinks. I will continue to look, thanks for some direction
-Joe

Didn't see the rest of your thread because you didn't stay in it but have
you tried a simple conditional format
format>conditional format

--
Don Guillett
SalesAid Software
[email protected]
"jrd269" <[email protected]> wrote in
message news:[email protected]...
>
> from what the groups say, it looks like it cannot be done. which really
> stinks. I will continue to look, thanks for some direction
> -Joe
>
>
> --
> jrd269
> ------------------------------------------------------------------------
> jrd269's Profile:
http://www.excelforum.com/member.php...o&userid=23815
> View this thread: http://www.excelforum.com/showthread...hreadid=374661
>

yes i tried the simple conditional formatting, but only three conditions. Well 4 if you consider the default.
But I found this add in, that says it can support up to 30 conditional formats found at:
http://xldynamic.com/source/xld.CFPlus.Download.html
might be worth checking out. I am going to get clearance then look into it.
-Joe

That site works wonderfully. Dbl click the .xla file and install it, then you have to change a few marcos security settings and BAM it works. I setup 5 conditional formats with 5 different colors and it works. Great job to those people. Thank you lads.
-Joe

Or you could always use a worksheet change event and select case for as many
as desired.

--
Don Guillett
SalesAid Software
[email protected]
"jrd269" <[email protected]> wrote in
message news:[email protected]...
>
> yes i tried the simple conditional formatting, but only three
> conditions. Well 4 if you consider the default.
> But I found this add in, that says it can support up to 30 conditional
> formats found at:
> http://xldynamic.com/source/xld.CFPlus.Download.html
> might be worth checking out. I am going to get clearance then look into
> it.
> -Joe
>
>
> --
> jrd269
> ------------------------------------------------------------------------
> jrd269's Profile:
http://www.excelforum.com/member.php...o&userid=23815
> View this thread: http://www.excelforum.com/showthread...hreadid=374661
>

I looked into the select case, but i am not a programmer. this was an easier way for me, the nonprogrammer, to do multiple conditions.
-Joe