worksheet change two choice intersect-CodePudding

I'm trying to use two choices for an Intersect in Worksheet_Change but the code below does not work. Nothing happens when I run the macro, even when I use Not Intersect Is Nothing, it still does not work.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

    If Intersect(Target, Range("A3:A100")) Then
        MsgBox "column A" & Target.Value
    End If

    If Intersect(Target, Range("B3:B100")) Then
        MsgBox "column B" & Target.Value
    End If
End Sub

CodePudding user response：

1. Change If Target.Cells.Count > 1 Then Exit Sub to If Target.Cells.CountLarge > 1 Then Exit Sub. Explanation

2. Change If Intersect(Target, Range("A3:A100")) Then to If Not Intersect(Target, Range("A3:A100")) Is Nothing Then

3. Delete the first End If

4. Change If Intersect(Target, Range("B3:B100")) Then to ElseIf Not Intersect(Target, Range("B3:B100")) Is Nothing Then

5. You may want to add a space or a separator after the column name else the result will be concatenated with the column letter. For example, change MsgBox "column A" & Target.Value to MsgBox "Column A : " & Target.Value

So your code now becomes

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub

    If Not Intersect(Target, Range("A3:A100")) Is Nothing Then
        MsgBox "Column A : " & Target.Value
    ElseIf Not Intersect(Target, Range("B3:B100")) Is Nothing Then
        MsgBox "Column B : " & Target.Value
    End If
End Sub