(Excel 97)
I'm making a schedule template, and I'd like to use more than 3 conditional
formats to color code certain fields (vacation, sick, training, holiday,
etc.)

Also, I am using a data validation list to make it easier for the users to
just click in the appropriate entries. I'd like certain entries in one color
(such as holiday or vacation in blue background) and others in different,
various colors (training in green, for example). Only certain fields should
be formatted like this, and a free text entry should remain in the default
format.

I have some code working, but it appears that the items entered from the DV
list won't drive the vb formatting...

Private Sub Worksheet_Change(ByVal Target As Range)
Set InRange = Intersect(ActiveSheet.Range("A1:A10, D1:100"), Target)

If Not InRange is Nothing then
If Target.Value = "A" Then
Target.Interior.ColorIndex = 3
ElseIf Target.Value = "B" Then
Target.Interior.ColorIndex = 4
ElseIf Target.Value = "C" Then
Target.Interior.ColorIndex = 5
End If
End if
End Sub

I sure would appreciate any help to make it easier.

Thanks in advance!

Debra Dalgleish has this note at:
http://www.contextures.com/xlDataVal08.html:

In Excel 97, selecting an item from a Data Validation dropdown list
does not trigger a Change event, unless the list items have been typed in
the Data Validation dialog box. In this version, you can add a button to
the worksheet, and run the code by clicking the button. To see an
example, go to the Sample Worksheets page, and under the Filters
heading, find Product List by Category, and download the
ProductsList97.xls file.

So if possible, can you just type the list into that Data|validation dialog?
(there is a limit on the total length of that entry, though.)

Bob Wall wrote:

(Excel 97)
I'm making a schedule template, and I'd like to use more than 3 conditional
formats to color code certain fields (vacation, sick, training, holiday,
etc.)

Also, I am using a data validation list to make it easier for the users to
just click in the appropriate entries. I'd like certain entries in one color
(such as holiday or vacation in blue background) and others in different,
various colors (training in green, for example). Only certain fields should
be formatted like this, and a free text entry should remain in the default
format.

I have some code working, but it appears that the items entered from the DV
list won't drive the vb formatting...

Private Sub Worksheet_Change(ByVal Target As Range)
Set InRange = Intersect(ActiveSheet.Range("A1:A10, D1:100"), Target)

If Not InRange is Nothing then
If Target.Value = "A" Then
Target.Interior.ColorIndex = 3
ElseIf Target.Value = "B" Then
Target.Interior.ColorIndex = 4
ElseIf Target.Value = "C" Then
Target.Interior.ColorIndex = 5
End If
End if
End Sub

I sure would appreciate any help to make it easier.

Thanks in advance!

--

Dave Peterson