G
Guest
How can I set more than three (3) conditions in the conditional formatting in
Excel?
Excel?
J
JulieD
Hi Giovanni
you have a couple of options:
1) Bob Phillips has an add-in available for download from his site
www.xldynamic.com/source/xld.CFPlus.Download.html
2) John McGimpsey has notes on his site on how to do up to 6 without VBA
http://www.mcgimpsey.com/excel/conditional6.html
3) Use VBA - here's an example:
the following code pasted into the "sheet module" of the sheet - right mouse
click on the sheet tab that you want the conditional formatting on and
choose view / code you should see on the top left of the VBE window your
file name in bold (if not try view / project explorer) and the sheet that
you were on selected ...that's the "sheet module" ...on the right you should
see some white space - copy & paste the code in there -
assuming you want the conditional formatting to work on cell B6
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6")) Is Nothing Then
With Target
Select Case .Value
Case 1: Range("B6").Font.ColorIndex = 4
Case 2: Range("B6").Font.ColorIndex = 3
Case 3: Range("B6").Font.ColorIndex = 0
Case 4: Range("B6").Font.ColorIndex = 6
Case 5: Range("B6").Font.ColorIndex = 13
Case 6: Range("B6").Font.ColorIndex = 46
Case 7: Range("B6").Font.ColorIndex = 11
Case 8: Range("B6").Font.ColorIndex = 7
Case 9: Range("B6").Font.ColorIndex = 55
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
--- this turns the font of B6 a different colour depending on what value
(between 1 & 9) is entered in the cell.
if you'ld like additonal help with your criteria & formatting statements,
please feel free to post back with more details.
Cheers
JulieD
you have a couple of options:
1) Bob Phillips has an add-in available for download from his site
www.xldynamic.com/source/xld.CFPlus.Download.html
2) John McGimpsey has notes on his site on how to do up to 6 without VBA
http://www.mcgimpsey.com/excel/conditional6.html
3) Use VBA - here's an example:
the following code pasted into the "sheet module" of the sheet - right mouse
click on the sheet tab that you want the conditional formatting on and
choose view / code you should see on the top left of the VBE window your
file name in bold (if not try view / project explorer) and the sheet that
you were on selected ...that's the "sheet module" ...on the right you should
see some white space - copy & paste the code in there -
assuming you want the conditional formatting to work on cell B6
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6")) Is Nothing Then
With Target
Select Case .Value
Case 1: Range("B6").Font.ColorIndex = 4
Case 2: Range("B6").Font.ColorIndex = 3
Case 3: Range("B6").Font.ColorIndex = 0
Case 4: Range("B6").Font.ColorIndex = 6
Case 5: Range("B6").Font.ColorIndex = 13
Case 6: Range("B6").Font.ColorIndex = 46
Case 7: Range("B6").Font.ColorIndex = 11
Case 8: Range("B6").Font.ColorIndex = 7
Case 9: Range("B6").Font.ColorIndex = 55
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
--- this turns the font of B6 a different colour depending on what value
(between 1 & 9) is entered in the cell.
if you'ld like additonal help with your criteria & formatting statements,
please feel free to post back with more details.
Cheers
JulieD
D
David Flick
"=?Utf-8?B?R2lvdmFubmkgRy4gSXRhbHk=?=" <Giovanni G.
(e-mail address removed)> allegedly wrote in
This is one of the things I am using at present. I just need to figure out
how to set and use more than one range.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Number As Variant
If Intersect(Target, Range("H3:HZ36")) Is Nothing Then Exit Sub
For Each cell In Target
Number = cell.Value
Select Case Number
Case "M", "m"
cell.Interior.ColorIndex = 45
cell.Font.ColorIndex = 45
Case "L", "l"
cell.Interior.ColorIndex = 32
cell.Font.ColorIndex = 32
Case "G", "g"
cell.Interior.ColorIndex = 15
cell.Font.ColorIndex = 15
Case "T", "t"
cell.Interior.ColorIndex = 4
cell.Font.ColorIndex = 4
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.ColorIndex = 1
End Select
Next
End Sub
(e-mail address removed)> allegedly wrote in
This is one of the things I am using at present. I just need to figure out
how to set and use more than one range.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Number As Variant
If Intersect(Target, Range("H3:HZ36")) Is Nothing Then Exit Sub
For Each cell In Target
Number = cell.Value
Select Case Number
Case "M", "m"
cell.Interior.ColorIndex = 45
cell.Font.ColorIndex = 45
Case "L", "l"
cell.Interior.ColorIndex = 32
cell.Font.ColorIndex = 32
Case "G", "g"
cell.Interior.ColorIndex = 15
cell.Font.ColorIndex = 15
Case "T", "t"
cell.Interior.ColorIndex = 4
cell.Font.ColorIndex = 4
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.ColorIndex = 1
End Select
Next
End Sub
D
Dave Peterson
Like this???
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Number As Variant
Dim myRng As Range
Dim myIntersect As Range
Dim cell As Range
Set myRng = Me.Range("H3:HZ36,B1:c9,A1:a99")
On Error Resume Next
Set myIntersect = Intersect(Target, myRng)
On Error GoTo 0
If myIntersect Is Nothing Then Exit Sub
For Each cell In myIntersect
Number = cell.Value
Select Case LCase(Number)
Case "m"
cell.Interior.ColorIndex = 45
cell.Font.ColorIndex = 45
Case "l"
cell.Interior.ColorIndex = 32
cell.Font.ColorIndex = 32
Case "g"
cell.Interior.ColorIndex = 15
cell.Font.ColorIndex = 15
Case "t"
cell.Interior.ColorIndex = 4
cell.Font.ColorIndex = 4
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.ColorIndex = 1
End Select
Next cell
End Sub
I changed your "select case" statement so that I only had to look at lower case
"numbers"???
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Number As Variant
Dim myRng As Range
Dim myIntersect As Range
Dim cell As Range
Set myRng = Me.Range("H3:HZ36,B1:c9,A1:a99")
On Error Resume Next
Set myIntersect = Intersect(Target, myRng)
On Error GoTo 0
If myIntersect Is Nothing Then Exit Sub
For Each cell In myIntersect
Number = cell.Value
Select Case LCase(Number)
Case "m"
cell.Interior.ColorIndex = 45
cell.Font.ColorIndex = 45
Case "l"
cell.Interior.ColorIndex = 32
cell.Font.ColorIndex = 32
Case "g"
cell.Interior.ColorIndex = 15
cell.Font.ColorIndex = 15
Case "t"
cell.Interior.ColorIndex = 4
cell.Font.ColorIndex = 4
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.ColorIndex = 1
End Select
Next cell
End Sub
I changed your "select case" statement so that I only had to look at lower case
"numbers"???
D
David Flick
Dave,
This seems to be just a bit faster than the routine I was using when the
worksheet gets to be a few MB in size. Since I am a bit (very) weak on VBA
I wonder if you might set me straight on another question?
What I would like to do is evaluate multiple conditions in different
ranges.
i.e.
1) Column B has a numeric value I would like to color code based on
content. Values are 1 through 6.
2) Columns C and D has four digit numeric values to evaluate for
conditional formatting.
3) Column H has an alpha-numeric value to evalute for conditional
formatting.
4) Columns M, N, and O have single character codes to evaluate for
conditional formatting.
I have tried copying the Sub down and changing what I thought were the
relevant components, but alas have not been able to fudge it along
sufficiently well to make it work as yet.
Dave Flick
D
Dave Peterson
One way to do it is check to see if the changed cell is in one of those ranges:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngB As Range
Dim myRngCD As Range
Dim myRngH As Range
Dim myRngMNO As Range
If Target.Cells.Count > 1 Then Exit Sub
Set myRngB = Me.Range("B:B")
Set myRngCD = Me.Range("C
")
Set myRngH = Me.Range("H")
Set myRngMNO = Me.Range("M:O")
If Not (Intersect(Target, myRngB) Is Nothing) Then
'do the work for column B
ElseIf Not (Intersect(Target, myRngCD) Is Nothing) Then
'do the work for C
ElseIf Not (Intersect(Target, myRngH) Is Nothing) Then
'do the work for H
ElseIf Not (Intersect(Target, myRngMNO) Is Nothing) Then
'do the work for M:O
End If
End Sub
==========
But since your ranges are complete columns, you could even make it look like:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case Is = 2
'do the work for B
Case Is = 3, 4
'do the work for C
Case Is = 8
'column H
Case Is = 13, 14, 15
'columns M, N, 0
End Select
End Sub
The "do the work" stuff will look a lot like the previous posts.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngB As Range
Dim myRngCD As Range
Dim myRngH As Range
Dim myRngMNO As Range
If Target.Cells.Count > 1 Then Exit Sub
Set myRngB = Me.Range("B:B")
Set myRngCD = Me.Range("C
")Set myRngH = Me.Range("H")
Set myRngMNO = Me.Range("M:O")
If Not (Intersect(Target, myRngB) Is Nothing) Then
'do the work for column B
ElseIf Not (Intersect(Target, myRngCD) Is Nothing) Then
'do the work for C
ElseIf Not (Intersect(Target, myRngH) Is Nothing) Then
'do the work for H
ElseIf Not (Intersect(Target, myRngMNO) Is Nothing) Then
'do the work for M:O
End If
End Sub
==========
But since your ranges are complete columns, you could even make it look like:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case Is = 2
'do the work for B
Case Is = 3, 4
'do the work for C
Case Is = 8
'column H
Case Is = 13, 14, 15
'columns M, N, 0
End Select
End Sub
The "do the work" stuff will look a lot like the previous posts.
David said: