More than 3 criteria in Conditional Formatting

  • Thread starter Thread starter juliejg1
  • Start date Start date
J

juliejg1

I would like to use the conditional formatting to color code the cell
depending on whether the cell says "1-High", "2-Med", "3-Low", "4-On Hold",
"5-Canceled". Can this be done?
 
You cannot add more than 3 conditions to Conditional Formatting, but you can
do what you want using VBA. Which cells do you want to have this
functionality and is what you showed us exactly what is in the cells (that
is, is the number, dash, description in the cell or just the number)?
 
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
Case 5: .Interior.ColorIndex = 46 'orange

End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
 
I assume you are not running Excel 2007 which allows more than 3 conditions.

In earlier versions you will need to employ VBA

Here is some event code to place in the sheet module.............right-click
on sheet tab and "View Code".

Paste the code into that module. Edit to suit.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("1-High", "2-Med", "3-Low", "4-On Hold", "5-Cancelled")
nums = Array(8, 9, 6, 3, 7)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub


Gord Dibben MS Excel MVP
 
The cells in A2:A452 contain this exactly:
1-High
2-Med
3-Low
4-On Hold
5-Closed

1-High Needs to be colored green
2-Med Needs to be colored yellow
3-Low Needs to be colored red
4-On Hold Needs to be colored orange
5-Closed Needs to be colored black
 
Perfeft!!! Thanks!

Bob Phillips said:
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
Case 5: .Interior.ColorIndex = 46 'orange

End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob
Click to expand...
 
I see you have your answer from one of the other respondents, so I'll leave
it at that.
 
Back
Top