Thanks for the code. It does suit my needs. But. how shall I alter the code with the following with it?
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C11:C" & Rows.Count), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("AllDates").AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:="", _
CopyToRange:=Sheets("StaffList").Range("C1"), Unique:=True
End If
Static runme As Boolean
If runme = False And Target.Column = 5 And Target.Row >= 11 Then
runme = True
Target.Offset(, -2).Value = Date
Target.Offset(, -1).Value = Time()
Else: runme = False
End If
If Target.Column = 8 And Target.Row >= 11 Then
Target.Offset(, 1).Formula = "=IF(DATEDIF(RC[-1],NOW(),""y"")>0,DATEDIF(RC[-1],NOW(),""y"") & "" Years"",IF(DATEDIF(RC[-1],NOW(),""m"")>0,DATEDIF(RC[-1],NOW(),""ym"") & "" Months"",DATEDIF(RC[-1],NOW(),""y"")))"
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub