How to use drop box selection to change cell color.

[arcticman141]

Howdy.. I am sure this has been answered somewhere, but after four hours of surfing I can't seem to get it. So

I need to have a drop box selection change the color of the cell, but not change the value of it. The options in the drop box will be text but I need the result to be a change of color in target cell. Also need to have other options change the text color only of the cell. I am pretty new with excel, have spent alot of time tonight researching to no avail. I am running excel 2000 but have access to 2007 also. And need this to run on multiple cells. I have created the drop box cells F1-F5 using Data Validation -> List, Example of what I'm looking for

Cell A1= 16

Drop Box Option- Tardy = Change A1 to Red Background
Drop Box Option- Absent = Change A1 to Yellow Background
Drop Box Opton - Attended = Change A1 to Blue Background
Drop Box Opton - 5th = Change A1 to Blue Text
Drop Box Opton - 6th = Change A1 to Red Text

Your help is much appreciated.

 

[meldoc]

Hello again

Do you mean something like this.

Excel Workbook
	A	B	C	D	E	F	G	H
1	16					Attended		Tardy
2								Absent
3								Attended
4								5th
5								6th
Sheet1

Excel 2010

With your sheet set out lik the above use the code below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub
With Range("A1")
    Select Case Target.Value
        Case "Tardy": .Interior.ColorIndex = 3
        Case "Absent": .Interior.ColorIndex = 6
        Case "Attended": .Interior.ColorIndex = 5
        Case "5th": .Font.ColorIndex = 5
        Case "6th": .Font.ColorIndex = 3
    End Select
End With
End Sub