Automatically add date when another cell is change

Question

I am trying to figure out a way to add the date and time to a cell when another cell is changed. For example I change sth in row 5, then C5 shall save the date and time when it was changed.

I found several entries to change colors on cells values etc, but was not able to find a solution for this problem yet.

Help is much appreciated. Thanks in advance!

Answer 1

So open the VBA editor and access the ThisWorkbook object.

Then with the drop-downs above the code window select 'Workbook' from the left hand one, and 'SheetChange' from the right hand one.

It should insert some code. Inside the Sub (before the End Sub code) - Add the following code:

Cells(Target.Row, 3).Value = Date + Time

The whole code in ThisWorkbook object;

Private Sub Workbook_SheetChange(ByVal Sh as Object, ByVal As Range)

    Cells(Target.Row, 3).Value = Date + Time

End Sub

NOTE: This will operate across ALL sheets. If you want to lock this down to specific sheets, insert the code into the Sheet object instead of the ThisWorkbook object, and remove the ByVal Sh as Object, from the arguments