You need to use VBA. Try this:-

Private Sub Worksheet_Calculate()
'Code must be placed in the codemodule of the actual sheet you are working
with.
Dim oCell As Range
For Each oCell In Range("A1:A20")
Select Case oCell.Value
Case Is < 1
oCell.Interior.ColorIndex = xlNone
Case Is = 1
oCell.Interior.ColorIndex = 5
Case Is = 2
oCell.Interior.ColorIndex = 3
Case Is = 3
oCell.Interior.ColorIndex = 6
Case Is = 4
oCell.Interior.ColorIndex = 4
Case Is = 5
oCell.Interior.ColorIndex = 7
Case Is = 6
oCell.Interior.ColorIndex = 15
Case Is = 7
oCell.Interior.ColorIndex = 40
Case Is 7
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Manan" wrote in message
...
Hi
I am new to conditional formating in Excel.

In row 2 I need to enter nos. between 1-5. I want each color to have a
particular color. I have managed to do 4 (3 with conditional formating and
the 4th retaining the default color).

Is there a way i can do all 5 colors?

Thanks