Date Last Modified (in cell)

[Jarmadon]

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?

 

[Man_of_Sleep]

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.

 

[Joe4]

Here is how I would do it:

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

 

[Man_of_Sleep]

wow.. you're genius. thanks a lot.

 

[Joe4]

You are welcome.

 

[divtjd]

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

 

[Joe4]

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.

 

[divtjd]

Thank you Joe!
I've posted a new thread with the details. Last Modified Date, Last Modified By, Last Modified Cell - of a cell in excel using VBA

- TJD