View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
JP[_4_] JP[_4_] is offline
external usenet poster
 
Posts: 897
Default Private Sub Worksheet_Change code not robust enough

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

On Jul 31, 12:58*pm, GI wrote:
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