Conditional formating / Cell Validation

Dave Nice

New Member
Joined
Sep 15, 2005
Messages
3
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

Legend
Joined
Sep 3, 2002
Messages
10,473
You could use change event code, coupled with data validation.
Book1
ABCDEFGH
1TheList
2success
3cancel
4user errorData Validation using List, =TheList
5otherbe sure to check Ignore blabks, in-cell dropdown
6
7Named 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.
 

Forum statistics

Threads
1,077,926
Messages
5,337,241
Members
399,133
Latest member
tamako

Some videos you may like

This Week's Hot Topics

Top