Colors for entries

[Ille]

Hi All,
I'm working wiht Excel 2003 and trying to set up a form. I will allow
entries to be selected from a list - so far no problem. Is it possible to
format the entries in advance, i.e. that for example, if you selcet "name"
from the list, it will appear in red?
Thanks for your help

 

[Joel]

Conditional formating will allow you to select a difffeent color for up to 3
different items. If your listt is greater than 3 you require a macro.

 

[Ille]

Unfortunately my list is longer ... no idea how to use a macro. Anyway
thanks a lot.

 

[Gord Dibben]

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("D1,E1,F1,G1"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "Able": Num = 10 'green
Case Is = "Baker": Num = 1 'black
Case Is = "Charie": Num = 5 'blue
Case Is = "Doofus": Num = 7 'magenta
Case Is = "Edgar": Num = 46 'orange
Case Is = "Francis": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Adjust range, names and colors to suit.


Gord Dibben MS Excel MVP