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 corre...
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 for the prompt response!