Status types in A1:A7

Numbers 37, 6, 45, 3, 4, 40 in B1:B7

Numbers 1, 1, 1, 2, 1, 3 in C1:C7

This event code in the sheet module.


Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Vals As Range
Dim R As Range
Dim RR As Range
Set R = Range("M21:M120")
Set Vals = Range("A1:C7") 'adjust to suit lookup range
If Intersect(Target, R) Is Nothing Then Exit Sub
On Error Resume Next
For Each RR In Intersect(Target, R)
RR.Interior.ColorIndex = Application.VLookup(RR.Value, Vals, 2, False)
RR.Font.ColorIndex = Application.VLookup(RR.Value, Vals, 3, False)
If RR.Value = "cancelled" Then
RR.Font.Bold = False
Else
RR.Font.Bold = True
End If
Next RR
End Sub


Gord Dibben MS Excel MVP


On Mon, 11 Jan 2010 02:55:01 -0800, dazzag82
wrote:

I have a spreadsheet for Scheduling Projects and one of the columns is to
display the Project Status. Cells M21 to M120 are the cells which I would
like to apply conditioonal formatting to depending on the status of the
Project. Status is entered into the cell via a drop down menu (data
validation).

How do I write a piece of VBA code which willow me to format 7 conditions.

The formats I would like for each status type are as follows

Planned (no format required)
Pre Work (Pale Blue Background, Black Bold Font)
Workshop (Yellow Background, Black Bold Font)
Post Workshop (Light Orange Background, Black Bold Font)
Past Due (Red Background, White Bold Font)
Complete (Bright Green Background, Black Bold Font)
Cancelled (Tan Background, Red plainText )

If anyone can help with the general structure of the code that would be
great. I have seen some examples using the following types of code:

cell.Interior. ColorIndex = 37

or

Target.Interior.Color = RGB(255, 0, 255)

I have no clue what to do and I do not understand how to figure out what the
colourindex or RGB values are so any help on that would be great also.

I also have an issue where the majority of users will have excel 2000 but I
also need it to work in 2003 for one or two people.

Thanks for your help.