Sorry my code is

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

Dim rng As Range

Set rng = Intersect(Target, Range("weekcal"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
On Error Resume Next
cl.Interior.ColorIndex = _
Application.WorksheetFunction.VLookup(cl.Value _
, ThisWorkbook.Sheets("Data1").Range("rngColour"), 2,
False)
cl.Font.ColorIndex = _
Application.WorksheetFunction.VLookup(cl.Value _
, ThisWorkbook.Sheets("Data1").Range("rngColour"), 3,
False)
If Err.Number < 0 Then
'cl.Interior.ColorIndex = xlNone
cl.Interior.ColorIndex = 2
End If
Next cl
End If

End Sub

I clicked on an old version....

wrote:
Hi

I've created a calendar using a VLOOKUP which shows key meetings and
events. I would like to colour code these, which I have done using
VBA, but I find that unless you physically go into the cell and press
return, the VBA code doesn't change the cell format.

My code is

Set rng = Intersect(Target, Range("weekcal"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
On Error Resume Next
' -- The line above won't change the cell's background
' -- color if the cell's value is not found in the range
' -- that we specified (rngcolors).
cl.Interior.ColorIndex = _
Application.WorksheetFunction.VLookup(cl.Value _
, ThisWorkbook.Sheets("Sheet2").Range("rngColours"), 2,
False)

If Err.Number < 0 Then
'cl.Interior.ColorIndex = xlNone
cl.Interior.ColorIndex = 2
End If
Next cl
End If

End Sub

Where I have created a range called "rngColours" and placed the
background colour and font colour in.

I have looked through some of the news groups and saw Tom Ogilvy's
reply to Todd Huttenstine using

DIM cell as Range, res as Variant
Worksheets(3).Activate
For each cell in worksheets(3).range("A5:A100")
res = Application.Vlookup(Cell.Value,_
Worksheets(4).Range("M2:Q100"),4,False
etc

I am very new to VBA and would like to know how I could incorporate
this into my code so when a drop down menu is chosen (to choose the
month) and the data changes, the background colour and font will also
change (without having to go into the cell and hit return).

Any help gratefully recieved.

Merry Christmas!

DeeCee