i don't see the logic here, seems to be missing some criteria, but maybe this
will give you an idea:

Sub Color_Cells()
Dim ws As Worksheet
Dim cell As Range
For Each ws In ThisWorkbook.Worksheets
For Each cell In ws.UsedRange
If IsDate(cell) Then
If cell < Now() And cell DateSerial(Year(Now()), _
Month(Now()) - 18, Day(Now())) Then
cell.Font.ColorIndex = 6
ElseIf cell < DateSerial(Year(Now()), Month(Now()) - 18, _
Day(Now()) + 1) Then
cell.Font.ColorIndex = 3
End If
End If
Next
Next
End Sub

--


Gary


"Darren Hastie" wrote in message
...
Hi All,

Im working on a large workbook with multiple worksheets. Each of these
worksheets has a large amount of data relating to training records. In
particular, the date that an employees certification is due to expire
(eg: 01/02/2007 or 31/09/2009 etc).

I need to color code these records with the below logic, eg:

If Cell Date is <Today() = Red Text or Cell
If Cell Date is <18months from Today()+1 = Yellow Text or Cell Cell
If Cell Date is 18months from Today() = No change - leave white

I have limited VBA knowledge, but will be able to work my way through
some of the code that is posted.

It's also key to note that there are multiple 'Blank' and 'Text' Cells
in these worksheets. I only want to apply this code to a cell if it is
populated with a date, is this possible ?? (eg: i only want to change
the color of the dates listed, not the other cells with text and single
numbers).

An extract of the spreadsheet can be provided if this will assist.

Thanks in advance for all of your help with this post.

Cheers,

Darren

(PS - My Newsgroup email is not legitimate due to potential SPAM emails
- please post a reply initially - Thanks)



*** Sent via Developersdex http://www.developersdex.com ***