Date Last Modified (in cell)

Jarmadon

New Member
Joined
Jun 10, 2009
Messages
6
I want to know if there is a way to have a cell beside a series of cells that will change its date (and time) when any of the cells in the series is changed.

On a single row, I have four materials that total to a fifth cell. The 6th cell in that row, I would like to have a "date last modified" that would change if any of the first five cell's value is changed.

Is this possible?
 
Thank for your reply Joe4.

I am monitoring Column F and Colum K.
Then I want to have the last date modified show at Colum AH (for Column F) and Column AI (for Column K).

I try using the above code, but only working for Column F.

May I know how to add the last date monitoring for the Column AI (Column K).

Thank you.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Here is how I would do it:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngF As Range
    Dim rngK As Range
    Dim cell As Range
    
'   Check to see if any updates to the ranges we are watching
    Set rngF = Intersect(Target, Columns("F:F"))
    Set rngK = Intersect(Target, Columns("K:K"))
    
'   Check to see if anything changed in column F
    If Not rngF Is Nothing Then
        For Each cell In rngF
            Cells(cell.Row, "AH") = Now()
        Next cell
    End If
    
'   Check to see if anything changed in column K
    If Not rngK Is Nothing Then
        For Each cell In rngK
            Cells(cell.Row, "AI") = Now()
        Next cell
    End If

End Sub
 
Upvote 0
There is no Function tht I am aware of that would do this. This is the only way I know of that you can do that for each change in your row. It is called an Event Procedure. RightClick the Sheet Tab and choose "View Code'. Copy and paste the code above to the white panel. Return to Excel(Alt+Q). Now, as written, whenever a cell in changed in Columns A:D, Rows 2 or down, the date and time will be entered in Column "F" in the same row as the changed cell. If those ranges don't fit your sheet, post back where the cells you want to monitor are and where you want the Date/Time. If you want, you could also place the name of the user who made the change. You can even add a comment to the changed cell with the time, who changed it ,and the previous value. If any of that has interest, post back.

lenze
Hi Lenze.. Thank you so much for the info. In the below example you gave can you please help by giving the line of code for adding a comment to the changed cell with the time, who changed it and the previous value.
Also, is there a way to use the Ctrl+Z option in the following code? - currently the Undo option is disabled if I run the below code.

Private Sub Worksheet_Change(byVal Target as Range)
If Target.Count > 1 Then exit Sub
If Target.Row < 2 Then Exit Sub
If Target.Column > 4 Then Exit Sub
Cells(Target.Row, 6) = Now
End Sub

- TJD
 
Upvote 0
Hi Lenze.. Thank you so much for the info. In the below example you gave can you please help by giving the line of code for adding a comment to the changed cell with the time, who changed it and the previous value.
Also, is there a way to use the Ctrl+Z option in the following code? - currently the Undo option is disabled if I run the below code.

Private Sub Worksheet_Change(byVal Target as Range)
If Target.Count > 1 Then exit Sub
If Target.Row < 2 Then Exit Sub
If Target.Column > 4 Then Exit Sub
Cells(Target.Row, 6) = Now
End Sub

- TJD
Lenze has not been on this board since 2012.

It is usually better to post your question to a new thread than posting to an old one, so it appears in the "Unanswered threads" listing that many people use to look for new, unanswered questions.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top