Home 
Search 
Today's Posts 
#1




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




conditional formatting for multiple sets of conditions
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 wrote: 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 
#3




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 
#4




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




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. 
#6




conditional formatting for multiple sets of conditions
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 steve wrote: 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 
#7




conditional formatting for multiple sets of conditions
perfect!

#8




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




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




conditional formatting for multiple sets of conditions
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 steve wrote: 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Multiple conditions for conditional formatting  Excel Worksheet Functions  
Multiple conditions in Conditional Formatting  Excel Discussion (Misc queries)  
Conditional Formatting for more than 3 conditions  Excel Worksheet Functions  
Conditional formatting: I have five conditions, how to do this?  Excel Discussion (Misc queries)  
Banding with Conditional Formatting with Multiple Conditions  Excel Worksheet Functions 