Drop Down lists and condtional formating

[grissom 345]

I have a spreadsheet for a 24 hour rota, i have added drop down boxes and
want to use conditional format the options, I have 5 selections on my drop
down list but formating will only allow me to colour 3, any way around it?

 

[OssieMac]

Can be done with macro code. If you want to use this option then please post
the following information.

The cell references to have the conditional format.
The 5 values in the drop downs. (If not the same for each cell reference
then need to match these with the cell references.)
The formatting required for each value.

 

[Shane Devenshire]

Hi,

1. You can get 5 formats if you don't mind that 2 of them will only be font
color changes by applying the three conditional formats and a custom format
in the Format, Cells, Number tab, Custom dialog box.

2. You can upgrade to 2007 which allows an unlimited number of conditional
format and much more.

3. You can use code such as the following:

Sub FormatCells()
Dim cell As Range
For Each cell In Selection
With cell.Interior
Select Case cell
Case Is < 0
.ColorIndex = 39
Case Is = 0
.ColorIndex = 37
Case 1 To 10
.ColorIndex = 34
Case 11 To 25
.ColorIndex = 35
Case Else
.ColorIndex = 40
End Select
End With
Next cell
End Sub