View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Steve Steve is offline
external usenet poster
 
Posts: 1,814
Default 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