Conditional Formatting More than 3 with Formula

[bugsyb6]

I've been searching for the last few days with no luck, so I thought I'd
post. Here's my situation:

I have a worksheet set up to track projects. In cell K3 I have the following
formula
=IF(AND(K$2>=$G3,K$2<=$I3,$E3="Broiler"),1,IF(AND(K$2>=$G3,K$2<=$I3,$E3="Hatchery"),2,IF(AND(K$2>=$G3,K$2<=$I3,$E3="Live
Haul"),3,IF(AND(K$2>=$G3,K$2<=$I3,$E3="Feed
Mill"),4,IF(AND(K$2>=$G3,K$2<=$I3,$E3="Breeder"),5,""))))). This formula is
copied through column IU and row 100.

I need to use conditional formatting to color the cell background and font
of the cells with the formula as follows: green (10) if the formula result is
1, violet (13) if the formula result is 2, red (3) if the formula result is
3, blue (5) if the formula result is 4, orange (46) if the formula result is
5, and white (0) if the formula result is "". I need this to happen if the
user adds or updates a value in column E, G, or I of the corresponding row.

I've tried putting together code from other posts, but I don't understand
what the code is doing well enough to make it work for me.

Any help anyone can provide is very appreciated.
bugsyb6

 

[ryguy7272]

One of these two Subs should do it for you:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0
Target.Interior.ColorIndex = 5
Case 0.33
Target.Interior.ColorIndex = 10
Case 0.66
Target.Interior.ColorIndex = 6
Case 1
Target.Interior.ColorIndex = 46
End Select
End If
End Sub

This is 'event code'. To use it, right click on your sheet/tab, and paste
it into the window that opens.

HTH,
Ryan---

 

[bugsyb6]

Thanks for the fast reply. I modified the code to fit my range and it works
if I double-click in the formula cell and then hit enter (in essence
re-entering the formula), but it doesn't change the color of the formula cell
if I add or update a value in column E, G, or I of the corresponding row -
which I need it to do.

Any ideas how to get the color to change if the value of the formula changes?
bugsyb6

 

[ryguy7272]

Hmmm, that doesn't sound right. Are you sure the range in the code covers
the used range (the range you are using on your sheet)?

Tools > Options > Calculation > Automatic

HTH,
Ryan---

 

[bugsyb6]

Sorry I didn't get back right away - busy weekend. But yes, the range
includes the cells with the formulas. I've even tried including different
ranges -like A1:IV25- just to see and my formulas update based on what I type
in column E but the color of the formula cell doesn't change. I have
calculation set to Automatic. Any more ideas?

 

[Gord Dibben]

Use a Calculation event instead of change event.

Private Sub Worksheet_Calculate()
stuff to do
End Sub


Gord Dibben MS Excel MVP

 

[Bernd P]

Hello,

Do you know that with Excel 2007 you can apply more than 3 conditional
formats?

Regards,
Bernd

 

[bugsyb]

When I put the code Ryan gave me into a Calculation event, I get an error for
the Target. Do I need to declare Target As something, or does the code need
to be modified to not include the Target? Sorry, I'm just not very good at
VBA yet (but I am learning).

p.s. if this posts twice I apologize - it's giving me issues!

 

[bugsyb]

Bernd -
Thanks for the info but, unfortunately, my company is still using Excel 2003
with no word on when they are upgrading.

bugsyb

 

[bugsyb]

I played around and got it to work. I changed the numbers to letters in my
formula and here's what I ended up with.

Private Sub Worksheet_Calculate()
Dim WatchRange As Range
Dim CellVal As String
Set WatchRange = Range("K3:IU100")

For Each Cell In WatchRange
If Cell.Value = "A" Then
Cell.Interior.ColorIndex = 10
Cell.Font.ColorIndex = 10
ElseIf Cell.Value = "B" Then
Cell.Interior.ColorIndex = 13
Cell.Font.ColorIndex = 13
ElseIf Cell.Value = "C" Then
Cell.Interior.ColorIndex = 3
Cell.Font.ColorIndex = 3
ElseIf Cell.Value = "D" Then
Cell.Interior.ColorIndex = 5
Cell.Font.ColorIndex = 5
ElseIf Cell.Value = "E" Then
Cell.Interior.ColorIndex = 6
Cell.Font.ColorIndex = 6
ElseIf Cell.Value = "" Then
Cell.Interior.ColorIndex = 0
Cell.Font.ColorIndex = 0
End If
Next Cell
End Sub

It may not be the best looking code ever, but it does the job! Thanks for
all of the help.