conditional formatting for multiple sets of conditions
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
"Dave Peterson" wrote:
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.
|