How do I write and "If....Cell is In Range" statement?

[qjd2004]

Hiya,

I'm currently using a long winded sub to monitor the contents of a cell. Is there any way I can shorten this to say If target (cell) is in range("whatever") because I have lot's of ranges and it's gonna make future development very slow otherwise.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Worksheet change event watcher 

Select Case Target.Address
   
      Case "$D$5", "$F$5", "$H$5", _
           "$J$5", "$L$5", "$N$5", "$P$5", "$R$5", _
           "$T$5", "$V$5", "$X$5", "$Z$5", "$AB$5"
    
         Select Case Target
               Case "y", "Y", "yes", "Yes", "YES", 1
                'init OnType to the applicable email format          
                OnType = 1
                'Initialise Payer to Trade Partner name as a String
                zuPayer = "InsertNameHere"
                'Call Sub method to notify 
                Call Notification_Email(OnType, zuPayer)
         End Select 
     Case "blah blah, etc"
etc etc

Is there any way I can do this without having to specify each cell in the range? I've tried just naming the range and refering to the name, but it doesn't seem to work! I think because target can only be a cell and it's then watching the whole range and not individual cell(s)?

 

[BrainB]

Something like :-

Set i = Application.Intersect(target, Range("Whatever"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
i.Select
End If


Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================