Forum Discussion
Update date when a certain cell is changed
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!
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- 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!! There is an issue though.
It gives me "argument not optional"Strange - it works for me. See the attached sample workbook.
