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.

Yes, they are. I am that fancy. 

P.S. I decided to make Table2 align with Table1 so I can extend Table1 and just have the one VBA that I know works in there. 

MDCKV 

Are your tables really named Table1 and Table2?

HansVogelaar Hmm, no dice. Here is the code modified accordingly.  Thoughts?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject
    Dim rng As Range
    Set tbl = Me.ListObjects("Table1")
    Set rng = Intersect(tbl.ListColumns("Balance").DataBodyRange, Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        rng.Offset(0, 3).Value = Now
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
    Set tbl = Me.ListObjects("Table2")
    Set rng = Intersect(tbl.ListColumns("Statement Date").DataBodyRange, Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        rng.Offset(0, 3).Value = Now
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

MDCKV 

See if you can modify this to match your setup:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject
    Dim rng As Range
    Set tbl = Me.ListObjects("Table1")
    Set rng = Intersect(tbl.ListColumns("Balance").DataBodyRange, Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        rng.Offset(0, 3).Value = Now
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
    ' *** Change the table name as needed ***
    Set tbl = Me.ListObjects("Table2")
    ' *** Change the column name as needed ***
    Set rng = Intersect(tbl.ListColumns("ColumnName").DataBodyRange, Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        ' *** Change the column offset 3 as needed ***
        rng.Offset(0, 3).Value = Now
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

peiyezhu as written, that did not work and I had tried other variations of the same thing with the obvious change of Table1 to Table2. I receive multiple errors with a variety of edits, including the one proposed here. If I put it after "End Sub" it's a problem. If I remove "End If" and "End Sub" and insert it right after the end of the last set of instructions I get "Block If without End If." 

HansVogelaar Thank you very much for the time you took to reply to this thread in such detail. After reading the entire thread, I was able to create the VBA I needed to make my situation work. I need to figure out how to have this same function for two tables on the same worksheet. You have contributed so much, so I'm not asking you to give me the answer but if it's no trouble, would you mind letting me know how to apply this to a second table in the same worksheet? Here is what I used:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject
    Dim rng As Range
    Set tbl = Me.ListObjects("Table1")
    Set rng = Intersect(tbl.ListColumns("Balance").DataBodyRange, Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        rng.Offset(0, 3).Value = Now
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Aldossary1017 

As far as I know, this is not possible in Excel Online, sorry.

HansVogelaar  Dear Hans thank you so much for the amazing explaining. I went through every step and it all went perfect. Moreover, will you be able to assist me or is there a solution for an online worksheet in excel 365 ? because I couldn't save the changes and I am working with a team on that excel worksheet please your support is appreciated 

RCFUN 

Like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cel As Range
    Set rng = Intersect(Range("K12:K" & Rows.Count), Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        rng.Offset(0, 2).Value = Date
        rng.Offset(0, 4).Value = Environ("Username")
        For Each cel In rng
            Select Case cel.Value
                Case Range("S5").Value, Range("S6").Value
                    cel.Offset(0, 1).Value = Date
            End Select
        Next cel
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

HansVogelaar 

This is awesome, 

I came up with the below.

Basically if K get changed at all it updates M with the Date and P with the username. 

K Value is a drop down menu of cells in S2-S7. 

What i also need is L to update the date if and only if K= S5 or S6

Is that something that can be esily added?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("K12:K" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Intersect(Range("K12:K" & Rows.Count), Target).Offset(0, 2).Value = Date
        Intersect(Range("K12:K" & Rows.Count), Target).Offset(0, 4).Value = Environ("Username")
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

ahawkins91 

That is an unavoidable consequence of running VBA code...

HansVogelaar that worked!! Thank you so much! 

I noticed that the undo function is no longer available. Do you know why? And a fix for that? 

ahawkins91 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C2:C" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Intersect(Range("C2:C" & Rows.Count), Target).Offset(0, 4).Value = Date
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

HansVogelaar Hi Hans! I’m trying to essentially do the same thing as the OP. I copied and pasted the VBA you provided, but it doesn’t seem to be working for me. Do you think that you could provide another VBA code for me please? I would like the date in  column G to update whenever column C is modified. I would like G2 to update when C2 is modified. G3 to update when C3 is modified, G4 to update when C4 is modified…so on and so fourth. Thank you in advance! 

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?