Highlight cell which has been edited recently?

[kvmike]

Is there any way to highlight a cell that has been edited recently? (e.g. within the last 14 days)
I am working with a report that potentially changes on a weekly basis which is shared on teams with some colleagues.
A lot of the time there may be minimal changes, but there is a lot of data so it can be difficult to notice things looking through manually.

I tried searching around but no luck so far

Is this something that can be done through VBA?

 

[winds]

Would the review changes feature not be sufficient or the highlighting is necessary?

 

[bebo021999]

One way: save history of change from sheet "Data" in a sheet named "log"
Sheet "Data":
I am testing with this: the range of cells those changing need to be fired: A1:A8 & C6:F22 (and more...)
A sub to records the original value in sheet "Log":
Insert/ new module. Assign this scrip in a button "Original"

Option Explicit
Sub origin() ' to update the new original data
Dim k&, rng As Range, cell As Range, data As Worksheet, Logs As Worksheet
Set data = Worksheets("Data"): Set Logs = Worksheets("Log")
Set rng = Union(data.Range("A1:A8"), data.Range("C6:F22"))
    For Each cell In rng
        k = k + 1
        Logs.Cells(k + 2, 2).Value = cell.Address(0, 0)
        Logs.Cells(k + 2, 3).Value = Date + Time
        Logs.Cells(k + 2, 4).Value = cell.Value
    Next
End Sub

Sheet "Log": to records any change from those cells, time by time
Put this code in sheet "Data" : worksheet_Change even

Private Sub Worksheet_Change(ByVal Target As Range) ' to records every change in yellow range
Dim k&, nw As String, f, rng As Range, cell As Range, data As Worksheet, Logs As Worksheet
Set data = Worksheets("Data"): Set Logs = Worksheets("Log")
Set rng = Union(data.Range("A1:A8"), data.Range("C6:F22")) 'add more ranges if needed
    If Not Intersect(Target, rng) Is Nothing Then
        nw = Target.Address(0, 0) 'address of changing cell
        Set f = Logs.Range("A:A").Find(nw)
        f.End(xlToRight).Offset(0, 1).Value = Date + Time
        f.End(xlToRight).Offset(0, 1).Value = Target
    End If
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J
1	2
2	2
3	3
4	4
5	5
6	6		90	10	11	12
7	7		13	14	15	16
8	8		17	18	19	20
9			300	22	23	24
10			25	26	27	28
11			29	30	31	32
12			33	34	35	36
13			37	38	39	40
14			41	42	43	44
15			45	46	47	48
16			49	50	51	52
17			53	54	55	56
18			57	58	59	60
19			61	62	63	64
20			65	66	67	68
21			69	70	71	72
22			73	74	75	76
23
Data

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	days of last change		Original Data		Log
2		Cell Address	TimeStamp	Original Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value	TimeStamp	Value
3		A1	22/03/2022 14:30:30	1
4		A2	22/03/2022 14:30:30	2
5		A3	22/03/2022 14:30:30	3
6		A4	22/03/2022 14:30:30	4
7		A5	22/03/2022 14:30:30	5
8		A6	22/03/2022 14:30:30	6
9		A7	22/03/2022 14:30:30	7
10		A8	22/03/2022 14:30:30	8
11		C6	22/03/2022 14:30:30	9
12		D6	22/03/2022 14:30:30	10
13		E6	22/03/2022 14:30:30	11
14		F6	22/03/2022 14:30:30	12
15		C7	22/03/2022 14:30:30	13
16		D7	22/03/2022 14:30:30	14
17		E7	22/03/2022 14:30:30	15
18		F7	22/03/2022 14:30:30	16
19		C8	22/03/2022 14:30:30	17
20		D8	22/03/2022 14:30:30	18
21		E8	22/03/2022 14:30:30	19
22		F8	22/03/2022 14:30:30	20
23	0	C9	22/03/2022 14:30:30	21	22/03/2022 15:12	200	22/03/2022 15:15	300
24		D9	22/03/2022 14:30:30	22
25		E9	22/03/2022 14:30:30	23
26		F9	22/03/2022 14:30:30	24
27		C10	22/03/2022 14:30:30	25
28		D10	22/03/2022 14:30:30	26
29		E10	22/03/2022 14:30:30	27
30		F10	22/03/2022 14:30:30	28
31		C11	22/03/2022 14:30:30	29
32		D11	22/03/2022 14:30:30	30
33		E11	22/03/2022 14:30:30	31
34		F11	22/03/2022 14:30:30	32
35		C12	22/03/2022 14:30:30	33
36		D12	22/03/2022 14:30:30	34
37		E12	22/03/2022 14:30:30	35
38		F12	22/03/2022 14:30:30	36
39		C13	22/03/2022 14:30:30	37
40		D13	22/03/2022 14:30:30	38
41		E13	22/03/2022 14:30:30	39
42		F13	22/03/2022 14:30:30	40
43		C14	22/03/2022 14:30:30	41
44		D14	22/03/2022 14:30:30	42
45		E14	22/03/2022 14:30:30	43
46		F14	22/03/2022 14:30:30	44
47		C15	22/03/2022 14:30:30	45
48		D15	22/03/2022 14:30:30	46
49		E15	22/03/2022 14:30:30	47
50		F15	22/03/2022 14:30:30	48
51		C16	22/03/2022 14:30:30	49
52		D16	22/03/2022 14:30:30	50
53		E16	22/03/2022 14:30:30	51
54		F16	22/03/2022 14:30:30	52
55		C17	22/03/2022 14:30:30	53
56		D17	22/03/2022 14:30:30	54
57		E17	22/03/2022 14:30:30	55
58		F17	22/03/2022 14:30:30	56
59		C18	22/03/2022 14:30:30	57
60		D18	22/03/2022 14:30:30	58
61		E18	22/03/2022 14:30:30	59
62		F18	22/03/2022 14:30:30	60
63		C19	22/03/2022 14:30:30	61
64		D19	22/03/2022 14:30:30	62
65		E19	22/03/2022 14:30:30	63
66		F19	22/03/2022 14:30:30	64
67		C20	22/03/2022 14:30:30	65
68		D20	22/03/2022 14:30:30	66
69		E20	22/03/2022 14:30:30	67
70		F20	22/03/2022 14:30:30	68
71		C21	22/03/2022 14:30:30	69
72		D21	22/03/2022 14:30:30	70
73		E21	22/03/2022 14:30:30	71
74		F21	22/03/2022 14:30:30	72
75		C22	22/03/2022 14:30:30	73
76		D22	22/03/2022 14:30:30	74
77		E22	22/03/2022 14:30:30	75
78		F22	22/03/2022 14:30:30	76
Log

Cell Formulas
Range	Formula
A3:A78	A3	=IFERROR(DATEDIF(C3,INDEX($E3:$AD3,AGGREGATE(14,6,(COLUMN($E$2:$AD$2)-4)/($E$2:$AD$2="TimeStamp")/($E3:$AD3>0),1)),"d"),"")

 

[DacEasy]

Hi, you may be able to save to a log file:
Just as I was posting bebo0219999 post came through, so you could use his code combined with mine to add the original data versus only seeing the range in the log file.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myString As String, FN As Byte
    Dim filePath As String, myFileName As String
    Dim cdt_time, ntTime, ntDate
    
    cdt_time = Now()
    ntTime = Format(cdt_time, "hh:mm:ss")
    ntDate = Format(cdt_time, "dd-MMM-yyyy")
    
    filePath = "c:\Sage\"
    myFileName = "logTest.txt"
    
    myString = ntDate & "   " & ntTime & "   " & Target.Address
    FN = 1
    Open filePath & myFileName For Append As #FN
    Print #FN, myString
    Close #FN
    
End Sub