adding 3 months just to month part of date and highlighting relevant cells
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
Re: adding 3 months just to month part of date and highlighting relevant cells
You have to intercept the change of the cell by using worksheet event.
Something like this:
VB Code:
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
Re: adding 3 months just to month part of date and highlighting relevant cells
Conditional Formatting?
Pls note that I do not have Winzip at this workplace (new job) so I've renamed to txt to upload.