How do I add fill colour to drop down boxes?
One way:
This code changes the background depending on the value in your DV cell: in
the example it is cell C1
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "C1:C1" '<=== change to your DV cell
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value '<== need to change test to reflect you DV
values
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
To use code: right-click on w/sheet tab, "View code" and copy/paste above code
HTH
"Liz" wrote:
I have created drop down boxes in Excel using the validation tool but I need
each option to have a different fill colour. How do I do this?
|