I've tried to use the Sub below (Posted by Julie D I think) as a work around
for more than three conditional formats and it works great IF I enter the
data directly in the target cells. But, if the taget range is populated by a
formula the color changes do not occur. Is there a way to modify this to
work when the cells in the target range are formula driven? (Or, maybe I'm
doing something wrong, any ideas?)



Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6:B10")) Is Nothing Then
With Target
Select Case .Value
Case 1: Target.Font.ColorIndex = 4
Case 2: Target.Font.ColorIndex = 3
Case 3: Target.Font.ColorIndex = 0
Case 4: Target.Font.ColorIndex = 6
Case 5: Target.Font.ColorIndex = 13
Case 6: Target.Font.ColorIndex = 46
Case 7: Target.Font.ColorIndex = 11
Case 8: Target.Font.ColorIndex = 7
Case 9: Target.Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub