Worksheet Change Event Wrongly Trigger

Hi,

I am writing Worksheet Change Event to enable auto trigger to clear contents from column "SC" to "TB" if user clear contents column "B" to "I".However, there is a bit error whereby the sheet will be triggered even if user clear contents for only one column between "B" to "I"

Before this, i have used If Range("B" & i & ":I" & i) Is Empty Then but it does not work

For the beginner like me, I am a bit confused on the term If Not Intersect(Target, Range()) Is Nothing Then


Here is my code.

Private Sub Worksheet_Change(ByVal Target As Range)

i = ActiveCell.Row

    If Not Intersect(Target, Range("B" & i & ":I" & i)) Is Nothing Then
       Range("SC" & i).Resize(1, 26).ClearContents
    End If
 
End Sub

Then, my next confusion is should I choose Worksheet SelectionChange or Change as both seems to have the same output

Try This...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
On Error GoTo Deb
Application.EnableEvents = False

For Each cell In Target
    If Target.Column >= 2 And Target.Column <= 9 And WorksheetFunction.CountBlank(Range("B" & Target.Row & ":I" & Target.Row)) = 8 Then
        Range("SC" & Target.Row).Resize(Target.Rows.Count, 26).ClearContents
    End If
Next cell

Deb:
    Application.EnableEvents = True
End Sub

Hi Debraj,

Thanks a lot


My last question is Worksheet SelectionChange or Change seems to have the same output. what is the differences?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    MsgBox "you have changed something... in A1"
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$1" Then
    MsgBox " hey.. you mave just move to another cell.. still you have not changed anything"
End If
End Sub

test this one in a blank sheet..