4 conditional formats based on dates

[kellyhell]

Looked and looked but can't find what I'm after.

I am after some code that changes the colour of any cell in column D based on dates.

The cell will change colour if the date is older than 12 months, 18 months, 2 years or 5 years. Different colour for each trigger date.

The code will trigger when the workbook opens and will check the sheet for the above triggers.

Thanks in advance

[Bob Phillips]

Excel 2007 or later, or excel 2003 or earlier?

[kellyhell]

Sorry - it's for 2002. I understand that 2007 has no limitation for CF.

[Bob Phillips]

Try this worksheet event code.




Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 Then

Select Case DateDiff("m", Target.Value, Date)

Case Is > 60: Target.Interior.ColorIndex = 3 'red

Case Is > 24: Target.Interior.ColorIndex = 5 'blue

Case Is > 18: Target.Interior.ColorIndex = 6 'yellow

Case Is > 12: Target.Interior.ColorIndex = 10 'green
End Select
End If
End Sub

[kellyhell]

Brilliant - thanks for that