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
--

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
--

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


"Bob Phillips" wrote:

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
--

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

Regards

Bob

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


"Bob Phillips" wrote:

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
--

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


"Bob Phillips" wrote:

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

Regards

Bob

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


"Bob Phillips" wrote:

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
--

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

--
HTH

Bob Phillips

"treetop40" wrote in message
...
Thanks that work great help. Do you have a colour code for the numbers to
use also please
--
Thanks
Jackie


"Bob Phillips" wrote:

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

Regards

Bob

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


"Bob Phillips" wrote:

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
--