Hi all, I am new to VBA and am trying to make a macro which captures a timestamp when an edit is made. Specifically, I want to show when a specific string is entered/changed.
For example, there are 4 stages for a project: Shipped, Testing, Assemble, and Queue. When Queue is changed to Assemble, I would like to see how long it remained in the Queue state before it was edited. When the same text is changed from Assemble to Testing, I would like to see how long it remained in the Assemble state before it was edited, and so on and so forth.
To give you an idea of the code I have now:
(Taken directly from https://www.teachexcel.com/talk/2826/troubleshooting-my-auto-timestamp-macro?nav=sim_side_col_forum with some edits)
Select AllPrivate Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
' TeachExcel.com
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range
'Your data table range
Set myTableRange = Range("L2:L1500")
'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
'Column for the date/time
Set myDateTimeRange = Range("S" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("T" & Target.Row)
'Stop events from running
Application.EnableEvents = False
'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then
myDateTimeRange.Value = Now
End If
'Update the updated date/time value
myUpdatedRange.Value = Now
'Update cell with username - 1 = Column A; 2 = Column B; etc.
Cells(Target.Row, 18).Value = "Last Edited By " & Application.UserName
'Turn events back on
Application.EnableEvents = True
End Sub
This works great, but only shows when an edit has been made irrespective of the specific set of words I am looking for.