Function for last modified date of cell

[TheBlackYellowHat]

First and foremost, thank you to everyone on this forum. I've already found a few answers by just searching. But in this case I'm stumped.

So this is what I've done so far (I'm by no means an expert).. this works but breaks in a certain situation.

I've got this code in Sheet1 of my VBAProject:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("O5:O300")) Is Nothing Then Exit Sub
    Target.Offset(0, -11) = Now()
    
End Sub

This checks if any cell in column O has been changed, and outputs todays date in the same row but in column D.

Like I said, it works, BUT, if I insert a new row anywhere beween O5 and O300, I get a runtime error 1004. Removing the row yields the same error.
Debugging points me to this line of code:

Target.Offset(0, -11) = Now()

This makes complete sense because that new row doesn't have any previous information so what's it checking? If I just click quit, then everything still works but it would be frustating to have an error pop up everytime a new row is added or deleted.

Sadly I have no idea how to figure out a workaround. So this is why I'm here. If anyone smarter and more experienced than me help, I would massively appreciate it! Thank you in advance to anyone who even reads this.

 

[kevin9999]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
        If Not Intersect(Target, Range("O5:O300")) Is Nothing Then
            Application.EnableEvents = False
            Target.Offset(0, -11) = Now()
            Application.EnableEvents = True
        End If
End Sub

 

[TheBlackYellowHat]

thank you so much kevin!!! sending lots of positive karma your way!

 

[kevin9999]

thank you so much kevin!!! sending lots of positive karma your way!

You're welcome, and thanks for the feedback