worksheet change two choice intersect

Question

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

What do you put as Target argument when you call Sub? If given range has more than 1 cell then you Exit Sub on first condition. — bracko, Dec 07 '22 at 07:36
Sub has an argument Target. When calling it, what do you put in Target, is it single cell for sure? — bracko, Dec 07 '22 at 07:41
@bracko it's an event handler, called when the user changes some cells on the worksheet. Target is set by what the cells the user changes. OP has explained they change only one cell, so Target will be a single cell. — chris neilsen, Dec 07 '22 at 07:44
i restarted the Excel application i found out all my macros stopped working but when i run the macro and change a cell in column A it throws a type mismatch error and when i change a cell in column B it throws a object variable or with block variable not set — k1dr0ck, Dec 07 '22 at 07:48

Siddharth Rout · Accepted Answer · 2022-12-07T07:55:29.803

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

Glad it worked for you :) – Siddharth Rout Dec 07 '22 at 07:55 — Siddharth Rout, Dec 07 '22 at 07:55

worksheet change two choice intersect

1 Answers1