run time error 1004 range of object _worksheet failed when creating automatic timestamp

Question

this is my first time using VBA so i am unable to solve this issue.

Im trying to create an automatic Timestamp for my excel document. The timestamp shows the input time and updated time in columns "AY" and "AZ", when any cell in the table is updated. However, the code works fine on my own computer, but once i share the document to my colleagues, they get:

"run time error 1004 range of object _worksheet failed"

Below is the code i use:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

Set MyTableRange = Range("A4:AZ100")

If Intersect(Target, MyTableRange) Is Nothing Then Exit Sub

Set myDateTimeRange = Range("AY" & Target.Row)
Set myUpdatedRange = Range("AZ" & Target.Row)

If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now
   
End If

myUpdatedRange.Value = Now

End Sub

Answer 1

Most probably the reason is that you change the affected worksheet that leeds to an infinite loop. Try this:

    On Error Goto errorlabel
    Application.EnableEvents = False
    If myDateTimeRange.Value = "" Then
         myDateTimeRange.Value = Now
    End If
    myUpdatedRange.Value = Now
    errorlabel: 
    Application.EnableEvents = True