awkwardjae
New Member
- Joined
- Jul 24, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
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
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