Maybe...

Option Explicit
Private Sub Worksheet_Calculate()

Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Range("b6:b10")

On Error Resume Next 'continue with next cell
For Each myCell In myRng.Cells
With myCell
Select Case .Value
Case 1: myCell.Font.ColorIndex = 4
Case 2: myCell.Font.ColorIndex = 3
Case 3: myCell.Font.ColorIndex = 0
Case 4: myCell.Font.ColorIndex = 6
Case 5: myCell.Font.ColorIndex = 13
Case 6: myCell.Font.ColorIndex = 46
Case 7: myCell.Font.ColorIndex = 11
Case 8: myCell.Font.ColorIndex = 7
Case 9: myCell.Font.ColorIndex = 55
End Select
End With
Next myCell

On Error GoTo 0

End Sub




RCW wrote:

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

--

Dave Peterson