Call a Macro for Long List of Cells

[fgiguere]

I'm making a worksheet witch included a popup calendar. So when I select a cell with tab, the arrows or by clicking it, it calls my macro. It's a macro named "OpenCalendar" that I previously made witch works fine.

My 1st question is, how can I call my macro for a long list of cells. Here's what I wrote:

Private Sub (Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$B$5", $B$6", $B$7" '...$B$76
Call OpenCalendar
End Select
End Sub
It works fine but I don't want to enter by "hand" each cells like $B$5, $B$6 etc in my procedure. Is there a quickest way to do that?

My 2nd question is, if in excel, I merged to cells together, is it possible to apply my procedure to the merged cell? And if so, for exemple, if I merged I4 with I5, how do I call this new cell? I4?

Thank you for your help!

 

[VoG]

Hello and welcome to MrExcel.

1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B5:B76")) Is Nothing Then
    Call OpenCalendar
End If
End Sub

2. Avoid merged cells - they cause havoc with VBA.

 

[fgiguere]

Thanks a lot, it works very well!!

Thanks for your time and you quick answers.