Can I rate a cell 1-5 and colour code it?

[Guest]

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.

 

[Bob Phillips]

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.

 

[Guest]

Thanks Phillip, but I do not understand event code, or able to follow your
reply, is it possible to explain in laymans terms please.

 

[Bob Phillips]

If you follow the instructions in the original post, and then try putting a
value in A1 you should see it work.

Regards

Bob

 

[Guest]

Thanks that work great help. Do you have a colour code for the numbers to
use also please

 

[Bob Phillips]

You used to be able to type Colorindex Property in VBA help and it would
list them, but that is not working on this XP version.

Run this codelet, it will list them on the active sheet.

Sub Colors()
Dim i As Long
For i = 1 To 56
Cells(i, "A").Value = i
Cells(i, "B").Interior.ColorIndex = i
Next i
End Sub