Forum Discussion

mister_hayman's avatar
mister_hayman
Copper Contributor
Oct 05, 2023

column last modified formula

So I have a spreadsheet and whenever column G is updated I would like to put a date timestamp in column P.

    • mister_hayman's avatar
      mister_hayman
      Copper Contributor

      SanthoshKunder yes I saw that - I've pretty much search this to death in the last 24hours. That particular solution would require me to right a formula on a row by row basis I think. Plus the thread got a bit in depth for me, involved a lot of trial an error for the contributors and didn't look like the issue was resolved.. It's not the succinct answer I'm looking for .

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        mister_hayman 

        • 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 the workbook.
        Private Sub Worksheet_Change(ByVal Target As Range)
            If Not Intersect(Range("G2:G" & Rows.Count), Target) Is Nothing Then
                Application.ScreenUpdating = False
                Application.EnableEvents = False
                Intersect(Range("G2:G" & Rows.Count), Target).Offset(0, 9).Value = Date ' or Now
                Application.EnableEvents = True
                Application.ScreenUpdating = True
            End If
        End Sub

Resources