View Single Post
  #5   Report Post  
Old August 11th 07, 01:18 AM posted to microsoft.public.excel.misc
Steve Steve is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,814
Default 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.