Update date when a certain cell is changed

[Sam_Chig]

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!

 

[#2]

@Sam_Chig

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.
Code:

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

[#3]

Thank you!! There is an issue though.

It gives me "argument not optional"

[#4]

@Sam_Chig 

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

[#5]

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.

[#6]

@Sam_Chig 

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

[#7]

@HansVogelaar 

[#8]

@Sam_Chig 

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.

[#9]

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

[#10]

@KraigKahl 

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.

[#11]

Thank you for the prompt response!