Hi everyone,

I have been using the code below to get time and date stamps in columns Q, R and S based on entries in col A and L.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 10 Then
Cells(Target.Row, 11).Activate
End If
    With Target
        If .Count > 1 Then Exit Sub
            'Update date-time stamp when a job is set in column A
            If Not Intersect(Range("a5:a5000"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 16).ClearContents
                Else
                    If .Offset(0, 16).Value = "" Then
                    With .Offset(0, 16)
                        .NumberFormat = "dd/mmm/yyyy - hh:mm:ss"
                        .Value = Now
                    End With
                    End If
                End If
                Application.EnableEvents = True
            'Update date-time stamp when job is completed in column l
            ElseIf Not Intersect(Range("l5:l5000"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 6).ClearContents
                    .Offset(0, 7).ClearContents
                Else
                    With .Offset(0, 6)
                        .NumberFormat = "dd/mmm/yyyy - hh:mm:ss"
                        .Value = Now
                    End With
                    'Calculate job duration in column N
                    .Offset(0, 7).NumberFormat = "dd - hh:mm:ss"
                    .Offset(0, 7) = .Offset(0, 6) - .Offset(0, 5)
                End If
            Application.EnableEvents = True
        End If
    End With
    
End Sub
I would like to change the second "sub-routine" to look at the result in column P instead and only put in the end date and time when col P results in an "OK". Is that possible?

The formula in col P is:
=IF($E5="","",(IF(AND($E5<>"Hafox",(OR($H5="",$I5="",$K5="",$L5=""))),"Incomplete",(IF(AND($E5="Hafox",(OR($K5="",$L5=""))),"Incomplete",(IF(AND($E5<>"Hafox",(OR($H5<($F5-49),$H5>($F5+49),$I5<($W5),$I5>($X5),$K5>(100),$L5<(2.2)))),"CALL ENGINEER","OK")))))))

Its a little unwieldy, but works OK with the conditional formatting I've also got running on this cell.

Can anyone show me a way forward please.

Many thanks in advance.
Gavin