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.

 

[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.

AMENDMENT TO THE OP:
Data entered in the stages for a corresponding project will be the date the project was completed

 

[Eric W]

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.

 

[ollie90065]

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.

Incredible! It worked perfectly! Remarkably concise too! Thank you so much!!

 

[Eric W]

Glad it worked for you!

Happy Holidays! ?

 

[justVBA125487]

Glad it worked for you!

Happy Holidays! ?

Ollie you are a living legend!! A modern day hero!!
I spent about 5 hours today trying to find a post on-line that would do specifically this, some only updated the date in offset columns, some did it only for changes in one column, YOU HAVE SAVED ME!!
Thank you

 

[nopala007]

Glad it worked for you!

Happy Holidays! ?

@Eric W Hi Eric, I have the exact same problem but as we are a company, we work on cloud based Microsoft Excel and other 365 tools, will this macro work on that environment?
Have you faced the same challenge?

 

[Joe4]

@Eric W Hi Eric, I have the exact same problem but as we are a company, we work on cloud based Microsoft Excel and other 365 tools, will this macro work on that environment?
Have you faced the same challenge?

Welcome to the Board!

Excel VBA/Macros only work on locally installed versions of Excel on the PC. It does not work on versions of Excel online.
I think you would either need to download the workbook to your PC, or you would need to do it another way, like by using Office Scripts instead of VBA, which does supposedly work in online versions of Excel.

I, personally, have written tons of VBA, but have never written any Office Scripts.
If you want to pursue that "Office Scripts" option, i would recommend starting a new thread with that question, as that would be significantly different from what is contained in this thread.
And then it would also appear as a new, unanswered questions for all to see.

 

[munthazir44]

Incredible! It worked perfectly! Remarkably concise too! Thank you so much!!

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.

Worked for me too.
How do I modify to work if I want to consider 3 rows to the left instead of right?

 

[Joe4]

Worked for me too.
How do I modify to work if I want to consider 3 rows to the left instead of right?

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?