| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| 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 | |||
Hybrid Mode
