Solution for conditional formatting issue

[Fleone]

I hope that I am posting this in the correct area. If not, please let me know
so I don't make the same mistake twice.

Just want to post something that I came up with with the help of several
members of the community here. I needed a way to apply more than 3
conditional formats to a range of cells and base those formatting changes on
the cells contents.
This is the code that I am using to solve this problem. Perhaps something
similar will help someone else in a similar situation.

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("E5:N77")) Is Nothing _
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Lunch": .Font.ColorIndex = 0
.Interior.ColorIndex = 6
Case "Off": .Font.ColorIndex = 1
.Interior.ColorIndex = 0
Case "Vac": .Font.ColorIndex = 2
.Interior.ColorIndex = 5
Case "Call Off": .Font.ColorIndex = 1
.Interior.ColorIndex = 45
Case "Holiday": .Font.ColorIndex = 0
.Interior.ColorIndex = 44
Case "Meeting": .Font.ColorIndex = 2
.Interior.ColorIndex = 54
Case "Project": .Font.ColorIndex = 2
.Interior.ColorIndex = 10
Case "Training": .Font.ColorIndex = 2
.Interior.ColorIndex = 48
Case "12-9": .Font.ColorIndex = 1
.Interior.ColorIndex = 0
Case "9-6": .Font.ColorIndex = 1
.Interior.ColorIndex = 0

End Select
End With
CleanUp:
Application.EnableEvents = True

End Sub

What this will do is in a specific range of cells in a worksheet, change the
cell color, and font color according to pre-determined text that is placed in
the cell.
Example: If the word "Lunch" is entered in a cell within the range E5 to
V77, that cell would be colored bright yellow and the font would be colored
black.

I would like to thank Bob, Tom, Patrick and any others that I might have
missed for their invaluable input on my dilemna.
I hope that this may help some others out there with similar needs.

Thanks!

 

[David McRitchie]

Hi Fleone,
Nice to see an attempt to post a solution waiting for someone with a problem, and it is
a programming thing so you are in the correct group all along.

Relevence of Subject Title:
Based on the wording of the subject title, I would consider this more a
continuation of your thread. The subject might have include the word Event macro
and Conditional Format limit of 3 to be more useful as a subject, since you now
have the advantage of knowing both the problem and the solution.
http://google.co.uk/groups?threadm=088F24DC-3322-4E5C-9A5F-15BAEE5CF191@microsoft.com

In any case the search engines will find your posting and it is sure to help
several people. Thanks for contributing.

 

[Fleone]

David,
Thank you for the feedback. I definitely see where this might have been
better posted on my original thread. I will try to keep a better handle on
the subject as well.
Thanks again for the good advice.

Frank Leone