You can use a VBA to accomplish this. Microsoft only allows 3 Conditional
Formats. Here is a code to accomplish the same task


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

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

ws_exit:
Application.EnableEvents = True
End Sub

This code needs to be placed in the worksheet that it will be used in, not a
module. So in retrospect, Alt+11, select "Sheet 1" and then copy and paste
this code.


--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009


"steve-o" wrote:

Hi All:

I would like to use conditional formatting on a spreadsheet where a row in a
spread sheet's color depends on the value of one of the cells on a row,
called the status cell. However, I would like to have six colors
representing six different values in the status cell. Is there a way to
implement this conditional formatting?

Thanks in advance,

steve-o