Case Statement using a Range of Conditions

[Andrew Bird via OfficeKB.com]

I need to apply a subroutine to a large number of cells within a
spreadsheet. I do not wish to type out individual case condition
statements for all the spreadsheet cells required. I am attempting to use
a case statement to call the subroutine and I am declaring the cells by
making use of a variable of type Range and by declaring the Range within a
Workbook event, in the following manner:

Public Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)

With ActiveSheet
Select Case Target.Address
Case "$C$6" To "$N$10", "$C$10", "$N$6"
Call ThisWorkbook.InsertComment(sheet)
Case "$C$13" To "$N$28", "$N$13"
Call ThisWorkbook.InsertComment(sheet)
Case Else
MsgBox "Not Allowed"
End Select
Cancel = True
End With

End Sub

First of all when declaring the Target.Address with 'Case "$C$6" To "$N
$10"', it seems to miss the two cells in the bottom left and top right
corners. Hence the reason why I have added the additional conditions "$C
$10" and "$N$6".

The second case statement, 'Case "$C$13" To "$N$28", "$N$13"', seems to
allow the application of the subroutine to cells that are out-with the
intended field. The intended field being "$C$13" To "$N$28".

Can anyone explain why this is happening and offer a suitable method to
allow the accurate allocation of the subroutine to only those cells
intended, without having to type a case condition for every cell required.

Regards

Aardvark

 

[Tom Ogilvy]

Public Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)


Dim rng1 as Range, rng2 as Range
With ActiveSheet
set rng1 = .Range("C6:N10")
set rng2 = .Range("C13:N28")
if not intersect(Target,rng1) is nothing then
Call ThisWorkbook.InsertComment(sheet)
Elseif not intersect(Target,rng2) is nothing then
Call ThisWorkbook.InsertComment(sheet)
else
MsgBox "Not Allowed"
End If
Cancel = True
End With

End Sub