conditional formatting for multiple sets of conditions
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
|