How to record a deleted row value in excel

[szcukg]

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.

[Aussiebear]

I've never heard of anyone wanting to "record a replaced row before as a value". Can you explain in greater detail, what the "value" is meant to be?

The following code by Alphafrog, will store as a value within a comment, any cell value replaced within a defined range ( in this case the range is all cells in columns A & B).

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oComment As Comment, cell As Range, strPrev As String
If Not Intersect(Target, Columns("A:B")) Is Nothing Then
For Each cell In Intersect(Target, Columns("A:B"))
Set oComment = Nothing
On Error Resume Next
Set oComment = cell.Comment
On Error GoTo 0
If Not oComment Is Nothing Then
strPrev = Mid(oComment.Text, InStr(oComment.Text, "Current value: ") + 15, 999)
oComment.Text Text:="Previous value: " & strPrev & Chr(10) & _
"Changed: " & Format(Now, "mmm dd, yyyy h:mm AM/PM") & Chr(10) & _
“Current value: " & cell.Value
ElseIf Not IsEmpty(cell) Then
cell.AddComment
cell.Comment.Text Text:="Previous value: Empty" & Chr(10) & _
"Changed: " & Format(Now, "mmm dd, yyyy h:mm AM/PM") & Chr(10) & _
"Current value: " & cell.Value
cell.Comment.Shape.Width = 150
cell.Comment.Shape.Height = 35
End If
Next cell
End If
End Sub

[/vba]

[szcukg]

I will explain what I have to do.I want to record insert update or delete events on any cell in my sheet.Now insert and update can be easily identified, but how do I know that a user deleted a whole row.Plus I need to say what was the previous value in this cell, even if I deleted the whole.Or record that a delete happened and reflect that with a comment or some fontcoloring.

[szcukg]

Your code is very good. A couple of points, it had one syntax mistake.I rectified and secondly it still doesn't tell me what was the previous deleted value

[Aussiebear]

Its not my code. As I indicated the code belongs to Alphafrog

[Bob Phillips]

Your code is very good. A couple of points, it had one syntax mistake.I rectified and secondly it still doesn't tell me what was the previous deleted value

Use the selectionchange event to capture the value before changing it.

[szcukg]

Use the selectionchange event to capture the value before changing it.

i tried it but it helps when I delete one cell in a row.Still haven't figured out how to do for an entire row.What I need is when I delete a row say row 3, then row3 should have a red color border and a comment on it which tells me the deleted value.

[Aussiebear]

If its a "value" as you continue to refer to, then you are basicily deleting a cell. If its a row your deleting, then it's more likely to be a combination of "values". As I asked you in post#2 "What is the value meant to be?" I've yet to see your description of this "value".

[szcukg]

If its a "value" as you continue to refer to, then you are basicily deleting a cell. If its a row your deleting, then it's more likely to be a combination of "values". As I asked you in post#2 "What is the value meant to be?" I've yet to see your description of this "value".

Ok my bad.I shoulld have kept it values.Yes I want to delete an entire row and that makes it a set of values.