Conditional formating / Cell Validation

[Dave Nice]

Hi,

I'm trying to create a spreadsheet that will enable my team to track an event based on customer on a day by day basis. The requirement is to mark a cell in a calendar style spreadsheet with a certain colur depending on the results for that customer each day.

The problem is I could do this with conditinal formating, but I only have three conditions to use, I need five... then I thought it would be better to use data validation via a drop down list. I'd like to display a brief description of the event, success, cancel, user error etc, but have the calendar style spreadsheet updated with a colour symbol. Question is, how do I select a valdiation option, but have it display a cell value/colour rather than the wording in the drop down list?

Any help would be appreciated.

Dave C

 

[just_jon]

You could use change event code, coupled with data validation.

Book1
	A	B	C	D	E	F	G	H
1	TheList
2	success
3	cancel
4	user error		Data Validation using List, =TheList
5	other		be sure to check Ignore blabks, in-cell dropdown
6
7	Named Range: TheList
8
Sheet4

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Excel.Range)<SPAN style="color:#00007F">If</SPAN> Intersect(Target, [C2])<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
Application.EnableEvents =<SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN><SPAN style="color:#00007F">Select</SPAN><SPAN style="color:#00007F">Case</SPAN> UCase(Target.Value)
    <SPAN style="color:#00007F">Case</SPAN> "SUCCESS"
        Target.Interior.ColorIndex = 3
    <SPAN style="color:#00007F">Case</SPAN> "CANCEL"
        Target.Interior.ColorIndex = 4
    <SPAN style="color:#00007F">Case</SPAN> "USER ERROR"
        Target.Interior.ColorIndex = 5
    <SPAN style="color:#00007F">Case</SPAN> "OTHER"
        Target.Interior.ColorIndex = 6
    Case<SPAN style="color:#00007F">Else</SPAN>
        Target.Interior.ColorIndex = xlNone<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Select</SPAN>
Target.Value = vbNullString
Application.ScreenUpdating =<SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Will overwrite the selected drop down with a color.