Last modified date for a range of cells

[DJPowelly]

Hi, I need a bit of help please.

I have a table that tracks learner progress. I would like to be able to show in a cell the last date any cell in a range was modified.
I have set this up as a table to allow others to add learners and not have to copy and past all of the formulas each time, so each time a new row is added the date has to relate to the cells in that row.

I would like to look at cells P4 to X4 and have the latest date that any of those cells were modified in Y4. When a new learner is added i would need Y5 to record the date from P5 to X5 and so on as new rows are added.

An alternative might be to take the date from O4 as this is calculated from cells P4 to X4 and should update whenever on of those cells changes.

I have looked at other VBA codes and they apply to the whole sheet or workbook and I would like to look at specific cells.

Any help would be greatly appreciated.

Many thanks

Keith

 

[rollis13]

Have been using this snippet for cells with formulas. Have a try and see if you can use it with your project. The macro has to be pasted in the sheet's module.
Since I find that it would be easier to check for changes of formulas in column O it already is coded for this. I presume that your are manually filling cells somewhere in columns A to N.
Otherwise attach (host link) a file with the exact structure of your sheet (just a few rows and no personal data).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vOld      As Variant
    If Not Range("O" & Target.Row) Is Nothing Then
        On Error GoTo Xit
        Application.EnableEvents = False
        Application.Undo
        vOld = Range("O" & Target.Row).Value
        Application.Undo
        If vOld <> Range("O" & Target.Row) Then Range("Y" & Target.Row) = Format(Now, "dd/mm/yyyy - hh:mm:ss")
    End If
Xit:
    Application.EnableEvents = True
End Sub

 

[DJPowelly]

Have been using this snippet for cells with formulas. Have a try and see if you can use it with your project. The macro has to be pasted in the sheet's module.
Since I find that it would be easier to check for changes of formulas in column O it already is coded for this. I presume that your are manually filling cells somewhere in columns A to N.
Otherwise attach (host link) a file with the exact structure of your sheet (just a few rows and no personal data).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vOld      As Variant
    If Not Range("O" & Target.Row) Is Nothing Then
        On Error GoTo Xit
        Application.EnableEvents = False
        Application.Undo
        vOld = Range("O" & Target.Row).Value
        Application.Undo
        If vOld <> Range("O" & Target.Row) Then Range("Y" & Target.Row) = Format(Now, "dd/mm/yyyy - hh:mm:ss")
    End If
Xit:
    Application.EnableEvents = True
End Sub

Hi, Thank you for the code, I have pasted it into the VBA editor for the sheet in question using the view code on the sheet tab at the bottom of the workbook, but I cannot get it to run.
If I click the run button, it does not show up in the list of macros.
sorry about this but i am new to using VBA coding.

 

[rollis13]

Oh, it's not to be run; I used event Worksheet_Change that will automatically trigger whenever a manual change is done somewhere in the sheet (presumably in column A to N where you will be inputting updated data). Then it will test for changes in column O and if any will time-stamp in column Y same row as column O.

 

[DJPowelly]

Thank you for the update.
The code works if I manually enter a value in column O, but if I update my workbook, it has no effect.
Column O is a sum total of columns P to X, which use a lookup from another worksheet to get the percentage for each column.
I have included the table below.
Thank you for your help with this, it is much appreciated.

Candidate Tracker V2 - table version with macro.xlsm
	O	P	Q	R	S	T	U	V	W	X	Y
3	Overall progress	QELTP3/001	QELTP3/002	QELTP3/003	QELTP3/004	QELTP3/005	QELTP3/006	QELTP3/007	AM2	Knowledge Units	Last modified
4	16%	50%	17%	36%	16%	15%	0%	0%	0%	0%
5	38%	68%	12%	32%	50%	67%	44%	0%	0%	0%
Progress_Overview

Cell Formulas
Range	Formula
O4:O5	O4	=(P4*0.0876)+(Q4*0.0837)+(R4*0.1315)+(S4*0.2072)+(T4*0.1195)+(U4*0.1912)+(V4*0.1594)+(W4*0.002)+(X4*0.0179)
P4:P5	P4	=XLOOKUP([@Name],'1605'!B:B,'1605'!CF:CF)
Q4:Q5	Q4	=XLOOKUP([@Name],'1605'!B:B,'1605'!EN:EN)
R4:R5	R4	=XLOOKUP([@Name],'1605'!B:B,'1605'!JB:JB)
S4:S5	S4	=XLOOKUP([@Name],'1605'!B:B,'1605'!PV:PV)
T4:T5	T4	=XLOOKUP([@Name],'1605'!B:B,'1605'!TH:TH)
U4:U5	U4	=XLOOKUP([@Name],'1605'!B:B,'1605'!YV:YV)
V4:V5	V4	=XLOOKUP([@Name],'1605'!B:B,'1605'!ADX:ADX)
W4:W5	W4	=XLOOKUP([@Name],'1605'!B:B,'1605'!ADZ:ADZ)
X4:X5	X4	=XLOOKUP([@Name],'1605'!B:B,'1605'!AEJ:AEJ)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O:Y	Cell Value	=1	text	NO

 

[rollis13]

Sorry, no idea how to detect this sort of formula update (=XLOOKUP). As said in post #2, I was presuming (and hoping) that the changes in ranges O and P:X were coming from manual changes somewhere in columns A:N since you were not showing them. Tried with event Worksheet_Calculate but can't get it to work properly.

 

[DJPowelly]

Thank you for trying, I really appreciate your help.

 

[DJPowelly]

Sorry, no idea how to detect this sort of formula update (=XLOOKUP). As said in post #2, I was presuming (and hoping) that the changes in ranges O and P:X were coming from manual changes somewhere in columns A:N since you were not showing them. Tried with event Worksheet_Calculate but can't get it to work properly

I have had an idea where I may be able to use this elsewhere in the workbook that does have manual inputs. but I would need to change column O to a range of columns.
Is that possible? if so how would I enter it as code?

 

[rollis13]

Will work as long as the manual input is on the same row where the time-stamp (column Y) will to be updated. If I guessed correctly, you are randomly inputting data in a Table (maybe even in another sheet); what I can't get is the exact row reference which comes with the result of (=XLOOKUP).

 

[DJPowelly]

Will work as long as the manual input is on the same row where the time-stamp (column Y) will to be updated. If I guessed correctly, you are randomly inputting data in a Table (maybe even in another sheet); what I can't get is the exact row reference which comes with the result of (=XLOOKUP).

On a separate table I manually input the learners work in multiple columns. I could add the code to that table as it is a manual entry.
From there I could use a lookup to add it to the overview table above.
The only thing is the manual entry could be in any cell in a range on the same row.
As the code you provided refers to column O I cannot get it to pickup changes in any other column.