VBA date stamp fails when values are copied over

[OlgaB]

Hi all,
I use the below code to add/update date/user in my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("j2:z1000000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("AH" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("AJ" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now
    myDateTimeRange.Offset(, 1).Value = Environ("username")

End If

'Update the updated date/time value
myUpdatedRange.Value = Now
myUpdatedRange.Offset(, 1).Value = Environ("username")


'Turn events back on
Application.EnableEvents = True
End Sub

However I have an issue when instead of updating the cells one by one the user copies value in, or just drags the previous value down.
Then the macros updates dates/user only for the first row in the changed range (in the attached example yes from the first row was dragged down)
Is there a way to fix it somehow?
Thanks a lot,
Olga.

 

[AlphaFrog]

Loop through the changed cells range

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range
Dim cell As Range

'Your data table range
Set myTableRange = Range("j2:z1000000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

For Each cell In Intersect(Target, myTableRange)

    'Column for the date/time
    Set myDateTimeRange = Range("AH" & cell.Row)
    'Column for last updated date/time
    Set myUpdatedRange = Range("AJ" & cell.Row)
   
    'Determine if the input date/time should change
    If myDateTimeRange.Value = "" Then
   
        myDateTimeRange.Value = Now
        myDateTimeRange.Offset(, 1).Value = Environ("username")
   
    End If
   
    'Update the updated date/time value
    myUpdatedRange.Value = Now
    myUpdatedRange.Offset(, 1).Value = Environ("username")

Next cell


'Turn events back on
Application.EnableEvents = True
End Sub

 

[OlgaB]

Thank you so much AlphaFrog! Worked perfectly well!​