If a cell contains the word "Completed", then return the date/time when it changed.

[awkwardjae]

Hi I have a excel form (mostly use it through sharepoint) where people submit a google form and request tasks for my team to complete. This google form autofills a row each time a request is submitted with different information not limited to submission date, ID, Name, Request etc. We have a status column (column S) in excel that contains one of "Not Started" default when request submitted, "In Progress" when someone enters their initial in "Prep" Column, "Not Tested" which we manually change if no do the request and "Completed" when we sign our initial in the "Done" Column AND "Prep" Column. All of this are automatically triggered (with exception of Not Tested). What I would like to include is a seperate column due to demand and audit reasons that says "Completed On" (column T) which essentially gets updated with the date and time that the Status column says "Completed".
Few things to note, would like this to apply automatically as requests are entered and status changes.
Currently we are on row 2751
If it cannot be applied retroactively that is fine, just for future requests
If possible if the status changes from Completed to another (due to someone accidentally signing) the completed on becomes blank.

I have tried:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

' See if any updates to specified range
Set rng = Intersect(Range("S2:S150000"), Target)

' Exit sub if no changes to watched range
If rng Is Nothing Then Exit Sub

' Loop through changed cells
Application.EnableEvents = False
For Each cell In rng
' See if cell is set to "Completed"
If cell.Value = "Completed" Then
' Add date stamp to column T of same row
cell.Offset(0, 1).Value = Now
End If
Next cell
Application.EnableEvents = True

End Sub

But does not seem to trigger the date showing.
Any help, advice, direction is appreciated. Thank you

 

[dreid1011]

Upon testing, this seems to work as expected. Do you have any other VBA code in the workbook? Either way, here is the code with the update to clear the cell if col S does not equal "Completed".

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

' See if any updates to specified range
Set rng = Intersect(Range("S2:S150000"), Target)

' Exit sub if no changes to watched range
If rng Is Nothing Then Exit Sub

' Loop through changed cells
Application.EnableEvents = False
For Each cell In rng
    ' See if cell is set to "Completed"
    If cell.Value = "Completed" Then
        ' Add date stamp to column T of same row
        cell.Offset(0, 1).Value = Now
    Else
        cell.Offset(0, 1).Value = ""
    End If
Next cell
Application.EnableEvents = True

End Sub