In some cases, it can be very useful to save the entire history of the change of a cell in its own note. It might look something like this:
To implement this, we need a simple macro. Right-click on the sheet tab where the monitored cells are located and select the command Source text (View Code). Copy and paste the following code into the window that opens:
Private Sub Worksheet_Change(ByVal Target As Range) Dim NewCellValue$, OldComment$ Dim cell As Range 'if cell is not in tracked range, then exit If Intersect(Target, Range("B3:B5")) Is Nothing Then Exit Sub 'iterate all cells in the modified region For Each cell In Intersect(Target, Range("B3:B5")) If IsEmpty(cell) Then NewCellValue = "Cell cleared" 'fix cell clearing Else NewCellValue = cell.Formula 'or its contents End If On Error Resume Next With cell OldComment = .Comment.Text & Chr(10) .Comment.Delete 'delete the old note (if any) .AddComment 'add a new one and enter text into it .Comment.Text Text:=OldComment & Application. UserName & " " & _ Format(Now, "MM.DD.YY h:MM:ss") & " : " & NewCellValue .Comment.Shape.TextFrame.AutoSize = True 'we autosize .Comment.Shape.TextFrame. Characters.Font.Size = 8 End With Next cell End Sub Change the tracking range B3:B5 to your own – and use it to your health.
- How to find out which users have logged into your Excel workbook recently
- Basic ways to set protection (cells, sheets, workbook) in Excel