More Than 3 Conditional Formats

S

SamuelT

Hi all,

I've got a worksheet that is showing five seperate workstreams. For the
column where the workstream is displayed I want the cell to turn a
specific color depending on which stream is chosen.

With three or less this would be simple - just use conditional
formatting, but Excel only allows up to three such criteria.

I've tried using an OR statement in "Formula is" to force at least have
some color in the fourth and fifth workstreams:

=OR("HS","BC 55")

But Excel's having none of it.

Not sure if there is a way to get around this. If anyone has a
suggestion it would be much appreciated.

TIA,

SamuelT
 
B

Bob Phillips

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

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

(replace somewhere in email address with gmail if mailing direct)
 
Top