Cell Background Color Change according to numerical value

  • Thread starter Thread starter jrd269
  • Start date Start date
J

jrd269

Conditional formatting will allow upto 3 conditions. I have five. Ho
can you write an IF() statement/s to allow numerical values to chang
cell background color. 1-green, 2-blue, 3-yellow up to 5. Also how wil
you nest multiple IF's, the help files, aren't helping.
-Jo
 
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
 
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
 
Or you could always use a worksheet change event and select case for as many
as desired.
 
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
 
I looked into the select case, but i am not a programmer. this was a
easier way for me, the nonprogrammer, to do multiple conditions.
-Jo
 
Back
Top