Date of Last Modified for a Cell

washcaps2 I HansVogelaar I got up to this point following along but I am getting the 12:00 AM error. What was the solution to give the actual time? Thank you.

Faytax 

Make the folder containing the workbook a Trusted Location for Excel.

File > Options > Trust Center > Trust Center Settings... > Trusted Locations.

Faytax 

See the attached version. To view/edit the code, right-click the sheet tab and select View Code from the context menu.

Faytax 

Thanks!

You got the formula the wrong way round, assuming that you want a timestamp in column D when the user enters a value in column D.

It will only work if column D is still empty when you create the formulas in column J. The formula in J5 should be

=IF(D5<>"",IF(J5<>"",J5,NOW()),"")

And remember that you have to enable iterative calculation in File > Options > Formulas.

If you want column J to be updated each time you edit column D, you need VBA code, not a formula.

HansVogelaar 

See attached the sheet. 

Another issue is that if one cell updates with the date then all cells update. 

https://1drv.ms/x/s!Avzs3jKduffg1hpOPBFgykz9_O0n?e=7a4cPA

Thanks so much for your assistance

Faytax 

In the formula in my first reply, A2 is the cell you want to track and A3 is the cell with the tine stamp.

If you cannot get it to work: could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

washcaps2 

Did you perhaps do something that caused the code to enter the date and time in ALL rows of column K? If so, do the following:

• Activate the Visual Basic Editor.
• Press Ctrl+G to activate the Immediate window.
• Type (or copy/paste) the line    Application.EnableEvents = False
• With the insertion point in that line, press Enter.
• Switch back to Excel.
• Select all rows from the row below the "real" used range of the sheet to the very last row 1048576.
• Right-click in the selection and select Delete.
• Activate the Visual Basic Editor.
• Press Ctrl+G to activate the Immediate window.
• Type (or copy/paste) the line    Application.EnableEvents = True
• With the insertion point in that line, press Enter.
• Switch back to Excel.
• Save the workbook.

HansVogelaar with this code currently on this sheet

HansVogelaar  Did this screenshot work?  It's the error message I get

washcaps2 

That must have another cause. The code doesn't impose a limit.

HansVogelaar 

Thanks, that worked.  Only problem I'm having is that I can't insert rows as it says the limit is exceeded.  I'll only ever need 200 rows or so.  Can I limit it to 200 rows instead of the million?

washcaps2 

Let's say you want the timestamp in column M.

Use this variation of the code posted higher up:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C2:C" & Rows.Count & ",E2:F" & Rows.Count & _
            ",J2:K" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Intersect(Range("M:M"), Intersect(Range("C2:C" & Rows.Count & _
            ",E2:F" & Rows.Count & ",J2:K" & Rows.Count), Target).EntireRow).Value = Now
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub