CODEWITHSUNDEEP
vbaexcel
Sandeep Thomas
Sandeep Thomas

Reputation: 4759

Check whether a changed cell in a named range excel vba

I just need to monitor changes in a worksheet, not in the whole worksheet, just inside a named range. So if any kind of change occurs in any cell it should check the change occurs in that named range and if so some function has to do.

Here is my code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ir As Boolean

    ir = Application.Intersect(Target.Address, Range("bd_main"))
    If ir = True Then
        MsgBox "change"
    End If

End Sub

But it triggers an error saying Type Mismatch in the Application.Intersect function for Target.Address

What I did wrong there which causes such error.

Upvotes: 0

Views: 2185

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

With slight modification, use the code below:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Range("bd_main")) Is Nothing Then
        MsgBox "change"
    End If

End Sub

Upvotes: 2

Related Questions