update last modified time per row targeted at a set column-CodePudding

I'm attempting to have Column K update with last modified date & time of its own row. I got close using the following code, but it modifies everything in the row after it when I only want it to change the Now value in Column K.

  Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:J")) Is Nothing Then
    Target.Offset(0, 1) = Now
 End If

End Sub

I know I have to change the Taege.Offset to something else, but what would that be to not break code/debug. I considered Target.Column, but I'm unsure of how to write the syntax.

Secondly, I'd like it to ignore row 1 & 2. I thought about changing Range("A:J") to Range("A3:J") but that also break/debugs.

All help is welcomed & appreciated.

CodePudding user response：

You can do it like this:

Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, c As Range
    
    Set rng = Application.Intersect(Target, Me.Range("A:J"))
    
    If Not rng Is Nothing Then
        Application.screenupading = False
        For Each c In rng.Cells
            c.EntireRow.Columns("K").Value = Now
        Next c
    End If
End Sub

CodePudding user response：

Maybe try something like this

Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("A:J")) Is Nothing Then
    For Each rng In Target.Rows
        If rng.Row > 2 Then
            Cells(rng.Row, 11).Value = Now
        End If
    Next rng
End If
End Sub

Perhaps a better solution would be this

Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Column < 11 Then
    Application.EnableEvents = False
    For Each rng In Target.Rows
        If rng.Row > 2 Then: Cells(rng.Row, 11).Value = Now
    Next rng
    Application.EnableEvents = True
End If
End Sub

A solution with no looping needed

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 11 Then ' 11 = K,
    Intersect(Range(Cells(3, 11), Cells(WorksheetFunction.Min( _
    Target.Rows.CountLarge   Target.Row - 1, Rows.CountLarge), 11)), _
    Target.EntireRow).Value = Now
End If
End Sub