Auto update a date

New Contributor

How do I have a cell auto update with the current date whenever a value in its row changes

Thanks

Dave

5 Replies

@maceslin 

Let's say that you want the current date/time in column M whenever a cell in columns A to L is changed by the user.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Range
    If Not Intersect(Range("A:L"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rw In Intersect(Range("A:L"), Target).Rows
            Range("M" & rw.Row).Value = Now
        Next rw
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open the workbook.

@Hans Vogelaar 

Works perfectly until I upload it to Google Docs for sharing with others.  It appears Google docs does not support macro-enabled workbooks.  Any other ideas

@maceslin 

VBA indeed doesn't work on the web. I don't know of a solution for that, sorry.

@Hans Vogelaar   thanks for the help

 

I know it won't help the OP, but if it's possible the user will be adding/deleting a good number of cells at one time, you could cut down on the macro run time by addressing column M as one range instead of one cell at a time. 

 

Private Sub Worksheet_Change(ByVal target As Range)
    If Not Intersect(Range("A:L"), target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Intersect(Intersect(Range("A:L"), target).EntireRow, Range("M:M")).Value = Now
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub