adding 3 months just to month part of date and highlighting relevant cells

[pauer100]

I have a list of employees and a start date wehn they joined the company.
what i am trying to do is enter a formula where if the date is 3 months later then the trianing date then that particaular cell is flagged and changes colour.
The hire date is formated as a medium date and i would like the relevant cells to be highlighted.

EmployeeName Hire Date TrainingDate Retrain
joe bloggs 29/07/2004 25/08/2004 No cell in red

i think i need to inlcude a if statment in the formula and try and add 3 to the months part of the date by just selecting the month from training date.

Does anyone know how to achieve this plz need some help

Thanks

Rich P

[D-niss]

You have to intercept the change of the cell by using worksheet event.
Something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Err_Worksheet_Change
Dim MyCell As Range
 
If Not Intersect(Target, Range("F2")) Is Nothing Then
Set MyCell = Range("F2")
 
    Application.EnableEvents = False
    
    If MyCell > DateAdd("M", 3, MyCell.Offset(0, 1)) Then
        With MyCell.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        End With
    End If
    Set MyCell = Nothing
End If
 
Err_Worksheet_Change:
Application.EnableEvents = True
Exit Sub
End Sub

[Ecniv]

Conditional Formatting?


Pls note that I do not have Winzip at this workplace (new job) so I've renamed to txt to upload.