How to record a deleted row value in excel

Question

I know how to get values which were updated or inserted in an excel sheet (read blank as previous value).I also want to record if a particular row was deleted and store that value also as a comment for the row that took it's place.

I know about the track changes option in excel, but I cannot use it.

Answer 1

In the Worksheet you can add this and it should do what you want.

As a way to get started.

You could additionally capture the existing comments if you like and append new comments to them, if you need to keep track of all changes.(that is not reflected here, but you might try that as well)

Public aCmt As String

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Text <> aCmt Then
 With ActiveCell
    If .Comment Is Nothing Then
    ActiveCell.AddComment
    ActiveCell.Comment.Text Text:=aCmt
    Else
    ActiveCell.Comment.Text Text:=aCmt
    End If
  End With
 End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
aCmt = ActiveCell.Text
End Sub