How do I add fill colour to drop down boxes?

[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?

 

[Guest]

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

 

[Lacty]

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?

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"

 

[Earl Kiosterud]

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...