Application.intersect always goes to nothing

I have a code where the target is the entire sheet, and the intersection ranges are 2 tables in the sheet but the code always considers as intersection is nothing but if I forcefully move the debugging line to the if/elseif lines it works right depending on where the change was made.

Anyone knows why this might be happening?

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NOH As Range
    Dim OOH As Range
    Set NOH = Range("NewOH")
    Set OOH = Range("OldOOH")
    If Not Intersect(Target, NOH) Is Nothing Then
        Application.EnableEvents = False
        Range("AB4").Value = Application.Username
        MsgBox "Confirm if 'Edited By' username is correct"
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, OOH) Is Nothing Then
        Application.EnableEvents = False
        Range("AB25").Value = Application.Username
        MsgBox "Confirm if 'Edited By' username is correct"
        Application.EnableEvents = True
    End If
End Sub

MVP

NOH and OOH are completely distinct (they don't have any cells in common), so Intersect(Target, NOH, OOH) will always be Nothing.

V-GEe7

Brass Contributor

Aug 02, 2022

Oh, That makes sense!
Is there anyway that I tell the program to say the intersection of Target AND (either NOH or OOH) and then go on to the corresponding IF statement?
I know that we have formulas in excel for "and" / "or" but I'm not very sure of how to use similar language with vba

HansVogelaar

MVP

Aug 02, 2022

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NOH As Range
    Dim OOH As Range
    Set NOH = Range("NewOH")
    Set OOH = Range("OldOOH")
    If Not Intersect(Target, NOH) Is Nothing Then
        Application.EnableEvents = False
        Range("AB4").Value = Application.Username
        MsgBox "Confirm if 'Edited By' username is correct"
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, OOH) Is Nothing Then
        Application.EnableEvents = False
        Range("AB25").Value = Application.Username
        MsgBox "Confirm if 'Edited By' username is correct"
        Application.EnableEvents = True
    End If
End Sub

V-GEe7

Forum Discussion

Application.intersect always goes to nothing

Share

Resources