Jackie,

Try event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3
Case 2: .Interior.ColorIndex = 5
Case 3: .Interior.ColorIndex = 6
Case 4: .Interior.ColorIndex = 10
Case 5: .Interior.ColorIndex = 35
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

"treetop40" wrote in message
...
I would like to be able to give a cell a rating of 1-5 and colour coded
but
conditional formatting in excel 2002 only allows up to 3, by using 1
number
as white cell means you can using a rating of 1-4. I required 1-5. I
have
tried to use coloured drop down menus but the cell colours do not carry
across.
Therefore can anyone tell me how I can type a number of 1 to 5 in a cell
and
that it will also show as a colour.
--
Many thanks
Jackie
--