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

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

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