|
Can I rate a cell 1-5 and colour code it?
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 -- |
| All times are GMT +1. The time now is 02:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com