Update date when a certain cell is changed

Copper Contributor

I want the current date to be updated when I change the Attempt # column. Date is column G and Attempt # is column I. 


To be clear, when I change attempt # in a cell, I want the Date Called corresponding to the same row to change to the current date. I'm not too familiar with macros, so any help will be much appreciated and help me be much more efficient!

 call list.PNG

10 Replies


Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the code window.
Close the Visual Basic Editor.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject
    Set tbl = Me.ListObjects(1) ' or Me.ListObjects("Table1") or similar
    If Not Intersect(tbl.ListColumns("Attempt #").DataBodyRange, Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Intersect(tbl.ListColumns("Attempt #").DataBodyRange, Target).Offset(0, -2).Value = Date
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
Thank you!! There is an issue though.

It gives me "argument not optional"


Strange - it works for me. See the attached sample workbook.

Interesting. I'm using excel through my company, but macros are enabled. The data is also in a table if that means anything. I'm not sure where the issue is.


Could you attach a copy of the problem workbook, without sensitive information?


Thank you! The procedure in the worksheet module is named worksheet_date. That won't work - it must be named Worksheet_Change in order to react to changes in the worksheet.

See the attached version.

Hi, is there a way to do this with a formula instead of a macro?


It's possible to use a formula to record the date/time when a cell is first changed, but the result won't change when the cell is changed again. The latter can only be accomplished through VBA or Office Script.

Thank you for the prompt response!