here it is:

Using VBA.
Add a standard code module and paste the following:

Option Explicit

Enum eColors
Yellow = 10092543
Green = 13434828
Blue = 16777164
Grey = 12632256
Orange = 10079487
End Enum

Sub MyConditionalFormatting(Target As Range)

Dim cell As Range
Dim clr As Long

For Each cell In Target


Select Case UCase(Trim(cell.Value))
Case "LUNCH": clr = eColors.Yellow
Case "OFF": clr = eColors.Blue
Case "HOLIDAY": clr = eColors.Grey
Case "COURSE": clr = eColors.Orange
Case Else: clr = eColors.Green
End Select

cell.Interior.Color = clr

Next

End Sub
Sub test()
MyConditionalFormatting Range("MyData")
End Sub

To test
On a sheet, select a range, name it "MyData" type some values into some
cells in th erange randomly, include: Holiday, Lunch, Off, Course

run the Test procedure. This passes the range "MyData" to the procedure that
then does the formatting.

This example allows four colors, but you can see that its quite easy to
adapt to use more colors.

HTH
Patrick Molloy
Microaoft Excel MVP



"Patrick Molloy" wrote in message
...
I have given several example over the past two weks of how this might be
done. do a search on this. try a serach "enum eColor" in this ng

"Infoseeker" wrote in message
...
Then is there a way to create a conditional format for more then 3 colors
where a range b47:b58 is red, b58:b69 is blue etc?

"keepITcool" wrote:

no..
datavalidation just creates a small, basic listbox
no customization possible 'in the box'.
(unless you're a wizard in APIs and subclassing)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Infoseeker wrote :

Hi

I used validation function to create a drop down list. b3 to b42
uses the same formaula that shows a list from b47:b172. I added 7 or
8 background colors within the b47:b172 cells. is there anyway to
get the background color to show when i select them in the b3 to b42
drop downs?