Dragging down a formula in VBA - last updated cell

[jkes813]

I am trying to make a column (Column AM) in an excel spreadsheet that automatically populates "Last Updated" only when any info within that row is updated (not the whole sheet).

The code I have in VBA populates a single cell, but I can't drag it down to fill in the rest. I would like to populate the whole column so that when any info in one row is updated, the last updated field in that row shows today's date.

The code I have for the single cell is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Set rng1 = Intersect([a3:al3], Target)
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
[am3] = Format(Now(), "mm-dd-yyyy hh:mm:ss")
Application.EnableEvents = True
End Sub

Thanks in advance for your help! I will use this all the time if this works!!

 

[Smitty]

Welcome to the Board!

You can expand the Target range to the full column area, then identify the Target.Row:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range
    Set rng1 = Intersect([A:AL], Target)
    If rng1 Is Nothing Then Exit Sub
    Cells(Target.Row, "AM").Value = Format(Now(), "mm-dd-yyyy hh:mm:ss")
End Sub

HTH,

 

[jkes813]

Thank you so much! Just what I needed!

Welcome to the Board!

You can expand the Target range to the full column area, then identify the Target.Row:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range
    Set rng1 = Intersect([A:AL], Target)
    If rng1 Is Nothing Then Exit Sub
    Cells(Target.Row, "AM").Value = Format(Now(), "mm-dd-yyyy hh:mm:ss")
End Sub

HTH,