I'm currently working with a worksheet change event procedure to update a change log when the cell value of a cell in a range is altered. Currently the procedure works fine for single cell entry and vertical pasting of data. For instance pasting a horizontal selection of cells into the range triggers the event correctly.
My problem is that the change event also triggers when a range of cells are pasted over the range of the change event and straddles the columns either side. For instance if a user was to paste a range of cells into the document that spanned AN47:AR47 the change event triggers. can this be stopped? Note I cant use the option of "If Target.Cells.count > 1 Then end sub" as I want the event to trigger when a single column of data is pasted into the range, such as AP46:AP56 etc
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG1 As Range
Dim RNG2 As Range
Set RNG1 = Sheet8.Range("$AP$46:$AP$145")
Set RNG2 = Sheet8.Range("$BA$46:$BA$145")
Application.ScreenUpdating = False (make it faster)
Application.Calculation = xlCalculationManual
If Not Intersect(Target, Union(RNG1, RNG2)) Is Nothing Then
If Target.Cells.count > 1 Then
TgValue = extractData(Target)
Else
TgValue = Array(Array(Target.Value, Target.Address(0, 0))) 'put the target range in an array (or as a string for a single cell)
boolOne = True
End If
etc etc
end sub
Leave the sub if the target has more than one column
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count > 1 Then Exit Sub