How do I get drop down menu's to show different background colour

[Ayd]

I am trying to create a drop down list of options using data Validation in
Excel 2003. I have set it up fine, but want the results to show the
background colour that I have selected for them.

For example, if option 1 is selected, the cell would turn green, option 2,
blue, etc.

Is this possible, and if so, how, as my list will currently only display the
text, and no colour?

Kind regards

 

[dlw]

from the menu bar, pick Format / Conditional Formatting...

 

[Jim Thomlinson]

Only the value will come through from the validation list and not the colour.
To make the colour change will require conditional formatting.

 

[L. Howard Kittle]

You can use VB code to do that. Try this sample, if you wish, and adjust to
suit your needs. Copy and paste into the worksheet VB editor.

Where the dropdown cell is in F1 and is a list of 1 to 6 options.

If you WANT to see the number selected from the dropdown, change the code
lines that look like this (Target.Font.ColorIndex = 3) to a different
ColorIndex number that will contrast with the Interior ColorIndex

Post back if you need help getting it set up. (By the way, the MVP's can
most likely whittle this solution down to a scant few lines of code using
Select Case or other magic only they know.) <G>

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
If Target.Row <> 1 Then Exit Sub
Target.Interior.ColorIndex = xlNone

If Range("F1").Value = 1 Then
With Target.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 3

ElseIf Range("F1").Value = 2 Then
With Target.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 41

ElseIf Range("F1").Value = 3 Then
With Target.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 4

ElseIf Range("F1").Value = 4 Then
With Target.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 46

ElseIf Range("F1").Value = 5 Then
With Target.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 6

ElseIf Range("F1").Value = 6 Then
With Target.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 38

End If
End Sub

HTH
Regards,
Howard