Conditional Formatting - More than 3 conditions

M

Madge

Do Microsoft have any plans to expand the number of conditions available. 3
is very limiting. Alternatively, are there any work-arounds for something as
simple as a rota for several different activities, all of which need colour
coding? Thanks.
 
T

Toppers

In Excel 2007 they number of conditions is increased ( I believe) but a "work
around" is the code below Or

see

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

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change this to suit your range


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
'etc.
End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub

Put this code in the w/sheet where you want the CFs. Right click on tab,
"View code" and copy/paste.

You will need to adjust the tests for your conditions as it currently tests
for values
1 to 4 (which can be extended).
 
M

Madge

Madge said:
Do Microsoft have any plans to expand the number of conditions available. 3
is very limiting. Alternatively, are there any work-arounds for something as
simple as a rota for several different activities, all of which need colour
coding? Thanks.
Click to expand...

Thanks. Looks like I need to update my version of Excel then!!
 
Top