ExcelBanter - Drop Down list with colours

Thanks Roger, and Chip, for replying.

I was playing around with it and found that I could conditionally format the
cell depending on the text selected - so if I choose "RED" it goes red. It's
just about enough to work for what I intend to use it for, but I'll
experiment with your suggestion in case I need to develop things a bit more.

Paul.

"Roger Govier" wrote:

Hi Paul

I don't know of any way of showing colours in the drop down list itself.
Regardless of how you have the list formatted, the DV list will only ever
show the plain text with no colours.

The following bit of rather "cludgy" code, will make the selection made, the
same colour as the source list colour - if that is any help.

The code needs to be added to the sheet on which you have your DV dropdowns.

Private Sub Worksheet_Change(ByVal Target As Range)

' assumes drop down validation list is in column C (3)
' Sheet on which validation list is held is Sheet1
' Validation List is called myList

If Target.Count 1 Then Exit Sub
If Target.Column =3 Then
Target.Cells.Interior.ColorIndex = _
WorksheetFunction.Index(Sheets("Sheet1").Range("my List"), _
WorksheetFunction.Match(Target.Value, Sheets("Sheet1"). _
Range("myList"), 0), 1).Cells.Interior.ColorIndex
Else
Target.Cells.Interior.ColorIndex = -4142
End If
End Sub

Change the column reference, Sheet name and Validation list name to suit
your own setup.

To use
Copy the code
Right click on sheet TabView Code
Paste into the white pane that appears
Alt+F11 to return to Excel

--
Regards
Roger Govier

"Paul" wrote in message
...
Hi,

Is there any way in Excel to create a drop down list which contains
colours?
For example to show RAG status (i.e. Red Amber Green).

At the moment I can only show text in the drop down list.

Would anyone know a way of working around this?

Thanks.