Emile, Try this then

'-----------------------------------------------------------------
Private Sub Worksheet_Calculate()
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"
Dim cell As Range

For Each cell In Me.Range(WS_RANGE)
With cell
Select Case .Value
Case 1: .Interior.ColorIndex = 6 'yellow
Case 2: .Interior.ColorIndex = 5 'blue
Case 3: .Interior.ColorIndex = 3 'red
Case 4: .Interior.ColorIndex = 10 'green
Case Else: .Interior.ColorIndex = xlColorIndexNone
End Select
End With
Next cell

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Emile" wrote in message
...
Bob:

Thank you very much. Works great as long as the cell value (1-9) is typed
in. In your example cell H1.

Unfortunately, my H1 value is a calculated value [Sum(f4:F31)] which
changes
as more values are added in F4:F31

Is ther a way to automatically update the color as the value in the cell
changes.

Thanks again