More than 3 criteria in Conditional Formatting

[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?

 

[Rick Rothstein]

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)?

 

[Jim Thomlinson]

There are a couple of options here...

http://www.xldynamic.com/source/xld.CFPlus.Download.html
http://www.mcgimpsey.com/excel/conditional6.html

 

[Bob Phillips]

'-----------------------------------------------------------------
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.

 

[Gord Dibben]

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

 

[juliejg1]

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

 

[juliejg1]

Perfeft!!! Thanks!

'-----------------------------------------------------------------
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

 

[Rick Rothstein]

I see you have your answer from one of the other respondents, so I'll leave
it at that.