Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell color based on cell value
I have 6 variables, they are 1, 2, 3, 4, 5 and 6.
I would like to have a cell have a dropdown menu where the user can select which of the six variables they want. Depending on which variable is chosen, that cell and the cell above it will change to a specific color that is associated with each variable. Thanks!!! Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell color based on cell value
Select the cell
data=Validation= select the list option. In the textbox put 1,2,3,4,5,6 click OK Right click on the sheet tab and select view code Assume the cell with the dropdown is B9. Put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim i as Long If Target.Count 1 Then Exit Sub If Target.Address = "$B$9" Then Set rng = Range("B8:B9") Select Case Target.Value Case 1 i = 3 Case 2 i = 7 Case 3 i = 2 Case 4 i = 12 Case 5 i = 9 Case 6 i = 4 Case Else i = xlNone End Select rng.Interior.ColorIndex = i End If End Sub -- Regards, Tom Ogilvy "Mr. Dan" wrote in message ... I have 6 variables, they are 1, 2, 3, 4, 5 and 6. I would like to have a cell have a dropdown menu where the user can select which of the six variables they want. Depending on which variable is chosen, that cell and the cell above it will change to a specific color that is associated with each variable. Thanks!!! Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell color based on cell value
Thanks Tom! Works perfectly!!!
"Tom Ogilvy" wrote: Select the cell data=Validation= select the list option. In the textbox put 1,2,3,4,5,6 click OK Right click on the sheet tab and select view code Assume the cell with the dropdown is B9. Put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim i as Long If Target.Count 1 Then Exit Sub If Target.Address = "$B$9" Then Set rng = Range("B8:B9") Select Case Target.Value Case 1 i = 3 Case 2 i = 7 Case 3 i = 2 Case 4 i = 12 Case 5 i = 9 Case 6 i = 4 Case Else i = xlNone End Select rng.Interior.ColorIndex = i End If End Sub -- Regards, Tom Ogilvy "Mr. Dan" wrote in message ... I have 6 variables, they are 1, 2, 3, 4, 5 and 6. I would like to have a cell have a dropdown menu where the user can select which of the six variables they want. Depending on which variable is chosen, that cell and the cell above it will change to a specific color that is associated with each variable. Thanks!!! Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the color of a cell based on the color of another cell. | Excel Discussion (Misc queries) | |||
Changing Cell Background Color based on data from another cell | Excel Discussion (Misc queries) | |||
Changing a color in a cell based on the text in another cell | Excel Discussion (Misc queries) | |||
changing value of a cell based on another cell color | Excel Discussion (Misc queries) | |||
Changing cell color based on its value | Excel Programming |