Auto Update Last Modified date when row is edited

[ollie90065]

Hi excel wizards! I'm looking for some assistance with a sheet to track up to 200 simultaneous projects by automatically updating the date in a cell when any cell in the row to the right of the date cell is modified. I have done lots of digging online and I understand that I will need a VBA code. I have tried to modify similar responses in related threads to no avail, but if there is one that is substantially related, please post a link!

Here is some additional info:
- all data is in one sheet.
- I have formatted the sheet with "Format as Table" over the range A2:AA245 purely for aesthetics and to automatically set Row 2 as a Header Row
- Rows represent distinct projects, with basic sheet info in Row 1
- All columns have header filters in row 2 to allow for sorting, but if sorting rows based on parameters like "name" and "ID #" or using Filters is not stable with VBA, then this feature could be removed in favor of this date functionality
- Project entries start at Row 3, and the amount of rows needed could be anywhere between 20-200
- Columns A:D represent basic project identifiers, and do not need to be included in the auto-update tracking
- E is the hopeful "Date Last Updated" column
- All columns after E represent distinct stages in a project
- the date should reflect the most recent update per row, and not refresh under any other circumstances (like saving or opening)
- the data entry that should trigger an update is all columns after E. If there needs to be a more specific range, I would want to be conservative and say columns F:AA
- data entry consists simply of entering "x" to represent that the respective column has been "accomplished" for the respective row.
- I would like to be able to add conditional formatting to the date column, and revise it at a later date.
- I would like to be able to revise column headers and add additional columns representing additional stages of a project. Ideally, these steps would appear in sequential order, so inserting a column at a later date should not constitute a change in any row
- Once a project is completed and I no longer need to track its progress, I will copy the row in to a second sheet for record keeping, and depending on what is recommended to preserve the VBA functionality I can either:
- A) "Clear Contents" of the row so as to recycle the row and keep the scale of this sheet to a minimum OR
- B) "Delete Row"
- Hopefully there is some ability to undo an update if data for a particular project is accidentally updated, but this is not essential
- I attached a screen capture to more clearly demonstrate my formatting and what an entry would look like. In this screen capture, I have only established two project stages, but I will need to add several, and sometimes insert additional stages in the middle.

 

[munthazir44]

Welcome to the Board!

I assume you mean "columns" and not "rows", as rows "go up and down" and columns go "left and right".

Which column exactly are you monitoring/watching, and which column do you want to get the date stamp?

I want the date stamp on Column E so no change on that but the condition should be every time a cell to the left of Column E is edited Column should update the date. Is this doable?

 

[Joe4]

I want the date stamp on Column E so no change on that but the condition should be every time a cell to the left of Column E is edited Column should update the date. Is this doable?

Yes, of course it is. But we really need to nail down the requirements. Otherwise the solution you get might not do exactly what you want.
Do you want column E updated when ANY column in A-D is updated, or just a specific column?

 

[munthazir44]

Yes, of course it is. But we really need to nail down the requirements. Otherwise the solution you get might not do exactly what you want.
Do you want column E updated when ANY column in A-D is updated, or just a specific column?

yes that's exactly what I need!

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.CountLarge > 1 Then Exit Sub
    
    If Target.Column > 4 Then Exit Sub

    Application.EnableEvents = False
    Cells(Target.Row, "E") = Date
    Application.EnableEvents = True
    
End Sub

 

[Clark97]

Welcome to the MrExcel forum!

A macro would be pretty easy. Open a copy of your workbook. On the sheet with the table, right click on the sheet tab on the bottom and select "View Code". In the window that opens, paste this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Row < 3 Then Exit Sub
    If Target.Column < 6 Then Exit Sub
    Cells(Target.Row, "E") = Date
End Sub

Close that window (Alt-Q, or use the X in the upper right corner). Try it out. It will add a date in column E for any changes found in rows 3 and up, columns 6 (F) and up. It will not make any changes if more than 1 cell was changed at a time, so you can add/delete rows/columns to your heart's desire.

There's no good way to undo a change like this, unless you want to stash the previous date in another column. Let us know how this works.

This was super helpful!! I'm pretty new to using Macros in excel and was wondering how one would use this to track all changes in a row and simultaneously track any changes to one specific cell in each row say column G, and store that data in column H. Registering changes in Column G would still be fine to track in the cell that tracks all of each row's changes, I would just like to keep a closer eye on when column G changes than on the rest.

 

[Joe4]

This was super helpful!! I'm pretty new to using Macros in excel and was wondering how one would use this to track all changes in a row and simultaneously track any changes to one specific cell in each row say column G, and store that data in column H. Registering changes in Column G would still be fine to track in the cell that tracks all of each row's changes, I would just like to keep a closer eye on when column G changes than on the rest.

Welcome to the Board!

So it sounds then like you need two tracking cells, right?
One to track changes to column G, and one to track changes to the other columns?

Is that correct?
If so, please tell us EXACTLY what other columns we are watching for changes, and what column we are storing that Date in (column H will store the last change in column G, but what other column will be used to store the last Date change for the other columns)?

 

[Clark97]

Welcome to the Board!

So it sounds then like you need two tracking cells, right?
One to track changes to column G, and one to track changes to the other columns?

Is that correct?
If so, please tell us EXACTLY what other columns we are watching for changes, and what column we are storing that Date in (column H will store the last change in column G, but what other column will be used to store the last Date change for the other columns)?

Yes, 2 tracking columns, I adjusted the original code by simply changing the E to a B, and the target column to be >3 in order to track changes in any column and store the date in column B, but I would also like to track changes in column G only and store them in H. Hopefully that makes sense.

 

[Joe4]

OK, so basically:
- updates to column G will update to column H
- updates to any other column (other than columns B, G, or H) will update column B

So we need to make sure we identify which columns are being updated, and update (or not update) the appropriate column.
The VBA could look something like this (I documented the code to explain what is happening in each step):

Private Sub Worksheet_Change(ByVal Target As Range)
    
'   Exit if more than one cell updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit it update prior to row 3
    If Target.Row < 3 Then Exit Sub
    
'   Disable events while we update cells
    Application.EnableEvents = False
    
'   Capture which column was updated
    Select Case Target.Column
'       Update column H if column G updated
        Case 7
            Target.Offset(0, 1).Value = Date
'       Ignore any changes to column B or column H
        Case 2, 8
            'Do nothing
'       Update column B
        Case Else
            Cells(Target.Row, "B").Value = Date
    End Select

'   Reeable events
    Application.EnableEvents = True
    
End Sub

 

[Clark97]

OK, so basically:
- updates to column G will update to column H
- updates to any other column (other than columns B, G, or H) will update column B

So we need to make sure we identify which columns are being updated, and update (or not update) the appropriate column.
The VBA could look something like this (I documented the code to explain what is happening in each step):

Private Sub Worksheet_Change(ByVal Target As Range)
   
'   Exit if more than one cell updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   Exit it update prior to row 3
    If Target.Row < 3 Then Exit Sub
   
'   Disable events while we update cells
    Application.EnableEvents = False
   
'   Capture which column was updated
    Select Case Target.Column
'       Update column H if column G updated
        Case 7
            Target.Offset(0, 1).Value = Date
'       Ignore any changes to column B or column H
        Case 2, 8
            'Do nothing
'       Update column B
        Case Else
            Cells(Target.Row, "B").Value = Date
    End Select

'   Reeable events
    Application.EnableEvents = True
   
End Sub

Sweet! Worked great and I think I learned a bit along the way! Thanks for the help!

 

[Joe4]

You are welcome.
Glad I was able to help!