How many conditions can I apply to a cell? I need to apply 8.

[Markus]

I have a annual leave spreadsheet where I want the user to enter a number
from 1-8 which will change the colour of the cell. Is this possible? How many
conditions can I apply to a cell? I need to apply 8.

Thanks in advance

Markus

 

[Mike H]

Hi,

One way. Right click your sheet tab, view code and paste this in. Play with
the colours to get what you require.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
Select Case Target.Value
Case Is = 1
icolour = 3
Case Is = 2
icolour = 4
Case Is = 3
icolour = 5
Case Is = 4
icolour = 6
Case Is = 5
icolour = 7
Case Is = 6
icolour = 8
Case Is = 7
icolour = 9
Case Is = 8
icolour = 10
Case Else
icolour = xlNone
End Select
Target.Interior.ColorIndex = icolour

End If
End Sub

Mike

 

[David Biddulph]

With Excel 2003, only 3 conditions (plus the base formatting).
If you want more than that, either Excel 2007 or VBA
http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm