conditional formatting for multiple sets of conditions

[steve]

Gord Dibben posted a solution to setup more than three format conditions (see
below), but it only works for a single set of conditions. Is it possible for
the sheet code to cover two distinct sets of conditions? E.g.:

Range("a1:a10,a20:a30"))
Case Is = "": Num = 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

Range("b15:b30,b55:b60"))
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

TIA.

Posted by Gord Dibben MS Excel MVP 2/6/2007 9:29 AM PST

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'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
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

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

 

[steve]

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]

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]

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]

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

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

On Error GoTo endit
Application.EnableEvents = False

For Each myCell In RngInput1.Cells
Num = 9999
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

For Each myCell In RngInput2.Cells
Num = 9999
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

endit:
Application.EnableEvents = True
End Sub

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

<<snipped>>

 

[steve]

perfect!

 

[steve]

Is there any reason this code works in one sheet but not another? I copied
the code from the original sheet and pasted it into a new sheet (via "view
code.") The only way to get the sheet code to do the formatting is to step
through (or run) it in the VB editor. Then the cells get formatted but the
formatting sticks even when changing the cell values. It's like the code is
applying the fomatting based on the cell's value at that point in time, but
then never checking to see if the value changes again. This is driving me up
the wall. The ONLY change to the code was the range to check. Thanks again.

 

[steve]

I think I'm figuring it out. The first solution Dave posted works if the
values are entered directly into the cells whereas the second solution works
if the target cells contain formulas that derive the cell values from
elsewhere. I need to play with this and see if I'm on the right track. If so,
no further discussion should be necessary. If not, or I figure something else
out, I'll post back here for the benefit of the board. steve.

 

[Dave Peterson]

Each sheet has a bunch of different events that you can tie into.

The first suggestion (that worked) tied into the worksheet_change event. This
event fires when the user makes a change to the worksheet by typing.

The second suggestion tied into the worksheet_calculate event. This event fires
when the worksheet recalculates.

Chip Pearson has some instructions on worksheet events:
http://www.cpearson.com/excel/events.htm

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm