Private Sub Worksheet_Change code not robust enough

[GI]

I have a file with data in columns A, B and C. For each row, the data
in column B is dependent on the data in column A. Therefore, if
someone changes a cell in column A, or deletes the entry, I want the
entry in the same row of column B to be deleted. I have the code
below. It works when I hit Delete in column A, but when I hit
Backspace, it either jumps over to column C and deletes the entry
there, or jumps down 1 row and deletes the entry in column B. Thank
you for the assistance.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 1 then
Exit Sub
End If

If Target.Column = 1
ActiveCell.Offset(0, 1).Select
Selection.ClearContent
End If

End Sub

 

[Dave Peterson]

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

if target.cells.count > 1 then exit sub 'one cell at a time

If Target.Column <> 1 then Exit Sub

'stop this procedure from calling itself
application.enableevents = false
target.offset(0,1).clearcontents
application.enableevents = true

End Sub

 

[JP]

Try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 1 Then
Exit Sub
End If

If Target.Column = 1 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
Application.EnableEvents = True
End If

End Sub


--JP