Track changes to a column, not when rows are inserted and deleted

[eesany]

Dear Kind Souls,

I have a problem that I need help on. I created a log in a file to track changes made to column C. This works fine when the user only updates cells in C. However, my users sometimes insert and delete rows, and these changes are also logged.

Can anyone advise how should I modify my macro to exclude changes due to rows insertion and deletion?

Here are my macros:


Dim CheckChange
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLastRow As Long
On Error Resume Next




If Target.Column = 3 And Target.Value <> CheckChange Then
lngLastRow = Sheet10.Range("A" & Rows.Count).End(xlUp).Row
Sheet10.Range("A" & lngLastRow + 1) = Format(Date, "mm-dd-yyyy")
Sheet10.Range("B" & lngLastRow + 1) = Format(Now, "h:mm:ss")
Sheet10.Range("C" & lngLastRow + 1) = Environ("UserName")
Sheet10.Range("D" & lngLastRow + 1) = CheckChange
Sheet10.Range("E" & lngLastRow + 1) = Target.Value
Sheet10.Range("F" & lngLastRow + 1) = ActiveSheet.Name
Sheet10.Range("G" & lngLastRow + 1) = Target.Address
Sheet10.Range("H" & lngLastRow + 1) = ActiveCell.Offset(-1).Offset(, -2)
Sheet10.Range("I" & lngLastRow + 1) = ActiveCell.Offset(-1).Offset(, 2)
Sheet10.Range("J" & lngLastRow + 1) = ActiveCell.Offset(-1).Offset(, 3)

End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CheckChange = Target.Value
End Sub

[SamT]

Try these changes

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CheckChange = ""

IF Target.Count <> 1 Or Target.Column <> 3 then Exit Sub

CheckChange = Target.Value

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If CheckChange = "" Then Exit Sub
Dim lngLastRow As Long
On Error Resume Next

If Target.Column = 3 And Target.Value <> CheckChange Then
'Blah blah, blah
End If
CheckChange = ""
End Sub

[eesany]

Thanks sam,

I think i got it with this additional line:
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

thanks for the idea.

[SamT]

You should consider all the possible things a User can do. I did.

[snb]

And ....


If Target.Column = 3 And Target.Value <> CheckChange Then
Sheet10.cells(Rows.Count,1)).End(xlUp)..offset(1).Resize(,10)=array(Format( Date, "mm-dd-yyyy"),Format(Now, "h:mm:ss"),Environ("UserName"),CheckChange,Target.Value,ActiveSheet.Name,Target.Address,ActiveCell.Offse t(-1,-2),ActiveCell.Offset(-1, 2),ActiveCell.Offset(-1, 3))
End If