How do I add fill colour to drop down boxes?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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?
Click to expand...


You can use conditional formatting so long as your list is not more
than 3

In the conditional formatting dialogue box select formula is: and
enter the following formula
=A1 = "Option 1" then format

Assuming your list starts at A1. Make sure you put the options in
"quotations"
 
Liz,

I'm pretty sure you can't do that. The only thing that comes to mind is to make your own
userform, with which you need to write macros to handle everything. A button on the
worksheet, similar to the dropdown button, would initiate your userform. It wouldn't appear
directly below the cell without some coaxing, though.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Back
Top