Excel: Add the date of the last modification on a row

Question

I'm trying to add the date and time of the last time a row was modified to a specific column of that row with the following VBA Script:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    ThisRow = Target.Row
    If Target.Row > 1 Then Range("K" & ThisRow).Value = Now()
End Sub

But it keeps throwing the following error:

Run-time error '-2147417848 (80010108)':

Method 'Value' of object 'Range' failed

Can anyone explain why this is happening?

Why do you have `Target As Excel.Range` instead of `Target As Range`? — nicholas79171, Jul 01 '15 at 15:12
Strange, I just tried both and they both work for me. Googling the error message doesn't help much either. Try something as simple as `Range("A1").Value = 5` and see if that gives you errors. — nicholas79171, Jul 01 '15 at 16:12
Don't consider it duplicate because after searching a lot I didn't come across that question. It is though related. Thank you a lot! ;-) — CMPSoares, Jul 01 '15 at 18:00
Definitely a duplicate. It's identical in cause, effect, and solution. It is the 3rd result in votes when [searching for](http://stackoverflow.com/search?tab=votes&q=%5bexcel%5d%20worksheet_change) `[excel] worksheet_change` on SO. Of course I link to that answer several times a week so I know how to find it. :) — Byron Wall, Jul 01 '15 at 18:05

score 1 · Accepted Answer · edited May 23 '17 at 11:46

You are creating an infinite loop by changing a value inside a Worksheet_Change event without disabling events first. When I do something similar, I get a range of errors from Out of stack space first to Method Range failed....

Do this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    ThisRow = Target.Row
    If Target.Row > 1 Then Range("K" & ThisRow).Value = Now()

    Application.EnableEvents = True
End Sub

Related post: MS Excel crashes when vba code runs

Excel: Add the date of the last modification on a row

1 Answers1