One possible way, assuming the values are calculated:

Private Sub Worksheet_Calculate()
Dim rCell As Range
For Each rCell In Range("A1:A10")
With rCell
Select Case .Value
Case Is = 1
.Interior.ColorIndex = 3
Case Is = 2
.Interior.ColorIndex = 5
Case Is = 3
.Interior.ColorIndex = 7
Case Is = 4
.Interior.ColorIndex = 9
Case Else
.Interior.ColorIndex = xlColorIndexNone
End Select
End With
Next rCell
End Sub


In article ,
Alison wrote:

Thanks, but I don't see know what code to use?

"JE McGimpsey" wrote:

One way is to upgrade to XL2007.

otherwise, you will probably have to use worksheet event code. Perhaps
something like this (put it in your worksheet code module: Right-click
on the worksheet tab and choose View Code):

There are tons of posts of ways to use event code in the archives:

http://groups.google.com/advanced_group_search?

(in addition to your search terms, enter *excel* (with asterisks) in the
group box)


In article ,
Alison wrote:

Is there any way to increase from "3" the number of conditional format
arguments allowed?

I have 4 arguments and the cells this applies to will contain one of the
4
arguments or be blank. If the cell is blank, no conditional formatting
should be applied. For each of the 4 arguments, a different cell pattern
colour would be applied.

Thanks!