Sorry, I noticed the Num's being the same, but I did see that the values were
different. (Stupid eyes!)

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

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

On Error GoTo endit
Application.EnableEvents = False

Num = 9999
If Not (RngInput1 Is Nothing) Then
For Each myCell In RngInput1.Cells
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
ElseIf Not (RngInput2 Is Nothing) Then
For Each myCell In RngInput2.Cells
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
End If

endit:
Application.EnableEvents = True
End Sub

A couple of things to watch out for.

The order is important in that "select case" structure. In your suggested code:

Case Is = "": Num = 2 'white
Case Is < 90: Num = 38 'red
Case Is < 80: Num = 36 'yellow
Case Is < 70: Num = 35 'green
Case Is < 50: Num = 34 'blue

If the value was 1, then it's less than 90 and you'd get Num = 38. The other
cases aren't even looked at.

And I changed your white fill to no fill. It may not be what you want, but I
always use no fill instead of white.

steve wrote:

Thanks for the quick reply Dave. Yes, it's the same five colors for both
ranges. I'm a bit confused though. Where do I put my conditions in your code?

To clarify a bit, the actual condtions are as follows:

Range("a1:a10,a20:a30"))
Case Is = "": Num = 2
Case Is < 40: Num = 38
Case Is < 42: Num = 36
Case Is < 44: Num = 35
Case Is 44: Num = 34

Range("b15:b30,b55:b60"))
Case Is = "": Num = 2
Case Is < 75: Num = 34
Case Is < 91: Num = 35
Case Is < 94: Num = 36
Case Is 94: Num = 38

Since case conditions are looked at in order (I think that's how it works),
the second range is never reached.

Correct me if I'm wrong, but in your code you have "Case Is = "Red": Num =
3". Wouldn't that format a cell if its value was the text string "Red"? None
of my cells have text values in them. Told you I was confused :)

Thanks again.

"Dave Peterson" wrote:

The colors are the same for each area, right?

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim RngInput As Range
Set RngInput = Intersect(Target, Me.Range("a1:a10,a20:a30,b15:b30,b55:b60"))

If RngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

For Each rng In RngInput.Cells
Num = 9999
'Determine the color
Select Case rng.Value
Case Is = "Red": Num = 3
Case Is = "Green": Num = 10
Case Is = "Yellow": Num = 6
Case Is = "Blue": Num = 5
End Select
If Num = 9999 Then
'do nothing
Else
'Apply the color
rng.Interior.ColorIndex = Num
End If
Next rng
endit:
Application.EnableEvents = True
End Sub

--

Dave Peterson


--

Dave Peterson