Forum Discussion
2 columns vba timestamp
Hello All, I would love to have code which inserts a timestamp in column G whenever column F is populated, and a timestamp in column I whenever column H is populated. Your help is greatly appreciated.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
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) If Not Intersect(Range("F:F,H:H"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Intersect(Range("F:F,H:H"), Target).Offset(0, 1).Value = Now Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
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) If Not Intersect(Range("F:F,H:H"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Intersect(Range("F:F,H:H"), Target).Offset(0, 1).Value = Now Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub- Many thanks Hans, that works like a dream, and you have written it in a way that I can change to include more columns if required.
Thanks again 🙂 Change the constant TheCells, for example:
Private Sub Worksheet_Change(ByVal Target As Range) Const TheCells = "F6:F1000,H6:H1000" If Not Intersect(Range(TheCells), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Intersect(Range(TheCells), Target).Offset(0, 1).Value = Now Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
