Code to produce color font in data validation selections

[Guest]

Here is code I use to create data validation by selecting text and then when
text is selected a code replaces the text and all works fine.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 12 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Eig Expense Coding").Range("B4") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"),
0), 0)
Application.EnableEvents = True
End If
End Sub


Sub MyFix()
Application.EnableEvents = True

End Sub

What kind of code do I need so that my selections would appear in different
font rather than the default black. I know you can't change font size or
list size but color would be nice to help distinguish between selections.
Below is 3 such descriptions I would like to have show up in differnet
colors.

Employee Training, Seminars, Management and
Professional Education Courses.
Employee Welfare, department lunch, food provided
for in-house training, Manager-Employee Lunch
Postage expense
Telephone (Not in Hotel)

Any help is appreciated.

 

[Tom Ogilvy]

Not supported in the Validation box itself. You can expand you code to
color the interior of the cell if you wish.

 

[Frank Stone]

hi,
the line of code you want is:
Range("??").Font.ColorIndex = 3 'red
to find out all of the colorindexes, in a blank workbook
copy, paste and run the following code.
Sub macGetColors()
' Macro written 2/10/02 by FSt1
Sheets("Sheet1").Select
Range("B2").Select
Set ci = Range("A1")
ci.Value = 1
Set c = Range("B2")
Do Until ci > 56
Set c2 = c.Offset(1, 0)
Set cnum = c.Offset(0, 1)
c.Interior.ColorIndex = ci.Value
c.Offset(0, 1) = ci.Value
ci.Value = ci.Value + 1
Set c = c2
c.Select
Loop
end sub
Regards
Frank

 

[Guest]

Here is what I tried with no luck. My list is under Codelist.(D5-D28) What
am I doing wrong? I thought I would try one line to start.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 12 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Eig Expense Coding").Range("B4") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"),
0), 0)
Range("D7").Font.ColorIndex = 3

Application.EnableEvents = True
End If
End Sub

hi,
the line of code you want is:
Range("??").Font.ColorIndex = 3 'red
to find out all of the colorindexes, in a blank workbook
copy, paste and run the following code.
Sub macGetColors()
' Macro written 2/10/02 by FSt1
Sheets("Sheet1").Select
Range("B2").Select
Set ci = Range("A1")
ci.Value = 1
Set c = Range("B2")
Do Until ci > 56
Set c2 = c.Offset(1, 0)
Set cnum = c.Offset(0, 1)
c.Interior.ColorIndex = ci.Value
c.Offset(0, 1) = ci.Value
ci.Value = ci.Value + 1
Set c = c2
c.Select
Loop
end sub
Regards
Frank

 

[Tom Ogilvy]

What is it you want to do?

It sounded like you wanted to color the entries in the dropdown (which isn't
possible). Do you want to color the cell that contains the dropdown based
on what selection is made?

--
Regards,
Tom Ogilvy

Here is what I tried with no luck. My list is under Codelist.(D5-D28) What
am I doing wrong? I thought I would try one line to start.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 12 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Eig Expense Coding").Range("B4") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"),
0), 0)
Range("D7").Font.ColorIndex = 3

Application.EnableEvents = True
End If
End Sub