Aug 02 2022 04:42 AM - edited Aug 02 2022 04:44 AM
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
V
Aug 02 2022 05:12 AM
NOH and OOH are completely distinct (they don't have any cells in common), so Intersect(Target, NOH, OOH) will always be Nothing.
Aug 02 2022 08:00 AM
Aug 02 2022 08:09 AM
Solution
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
Aug 02 2022 08:09 AM
Solution
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