Runtime 1004 error

[Mike_B]

Hi,

I have the below code in my worksheets which reads when a change is made to a cell in column L and outputs the date & time and the username to columns to the right. This works fine, however any time I paste in additional rows or delete a row, I get a runtime 1004 error, highlighting the Target.Offset rows as being problematic.

Any help would be appreciated!


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("L32:L5000")) Is Nothing Then Exit Sub
Target.Offset(0, 5) = Now()
Target.Offset(0, 6) = UserName()

End Sub

[SamT]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Or Target.Row < 32 Or Target.Column <> 12 Then Exit Sub
Target.Offset(0, 5) = Now()
Target.Offset(0, 6) = UserName()

End Sub

[Mike_B]

That fixes the runtime error, however now if I change multiple cells within column L at once, Now() and UserName() aren't called. It only works if I amend the cells in column L one at a time. Any ideas?

[Mike_B]

I suppose I can set Target.Count to a larger value.

[SamT]

I've never had to do this but try changing just Target.Count to Target.Columns.Count

[Aflatoon]

You should also really disable events if you're going to change cells in a change event.