Option Explicit
Option Compare Text
Private Sub Worksheet_Calculate()
Dim Num As Long
Dim myCell As Range
Dim RngInput1 As Range
Dim RngInput2 As Range

Set RngInput1 = Me.Range("a1:a10,a20:a30")
Set RngInput2 = Me.Range("b15:b30,b55:b60")

On Error GoTo endit
Application.EnableEvents = False

For Each myCell In RngInput1.Cells
Num = 9999
Select Case myCell.Value
Case Is = "": Num = xlNone '2 white
Case Is = 0: Num = 38 'red
Case Is = 1: Num = 36 'yellow
Case Is = 2: Num = 35 'green
Case Is = 3: Num = 34 'blue
End Select
If Num = 9999 Then
'do nothing
Else
'Apply the color
myCell.Interior.ColorIndex = Num
End If
Next myCell

For Each myCell In RngInput2.Cells
Num = 9999
Select Case myCell.Value
Case Is = "": Num = xlNone '2 white
Case Is < 50: Num = 34 'blue
Case Is < 70: Num = 35 'green
Case Is < 80: Num = 36 'yellow
Case Is < 90: Num = 38 'red
End Select
If Num = 9999 Then
'do nothing
Else
'Apply the color
myCell.Interior.ColorIndex = Num
End If
Next myCell

endit:
Application.EnableEvents = True
End Sub

steve wrote:

That did the trick Dave, but the ranges in question contain formulas that are
pulling data from a bunch of raw data. Is there a trick to make the
formatting take effect when the cells are not being filled in manually?

....steve

<<snipped