VBA -Code not working for 3 ranges

[RAJ1992]

got a code that allows you to select multiple values from a list. The below code works with 2 defined name sets (called "MY_RANGE" & MY_RANGE2") BUT stops when i add "MY_RANGE3"

Private Sub Worksheet_Change(ByVal Target As Range)

------
Dim OldVal As String
Dim NewVal As String

' If more than 1 cell is being changed
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

If Not Intersect(Target, ActiveSheet.Range("MY_RANGE", "MY_RANGE2")) Is Nothing Then
--------

HOWEVER

if i change the above line to ---- If Not Intersect(Target, ActiveSheet.Range("MY_RANGE", "MY_RANGE2", "MY_RANGE3")) Is Nothing Then ------

It stops working? any reason why?

 

[Sequoyah]

Hi RAJ1992,
the Intersect is not working because each parameter must have a common cell or cells. Try:

 If Not Intersect(Target, Union(ActiveSheet.Range("MY_RANGE", "MY_RANGE2", "MY_RANGE3"))) Is Nothing Then