If not Intersect(Target,Range("E11,H11,.........)) is nothing then

[pincivma]

Hi there

This should be an easy solve but I'm getting errors.

Here is a partial code that I got from the internet

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E11,H11,E482,H482,E953,H953.....")) Is Nothing Then 'Change this to any cell (or cells) you would like to have the Pop-Up Calendar Appear
CheckForSheet
CalendarShow
Else:
CheckForSheet
CalendarHide
End If
End Sub

I have over 200 cells in the If Not Intersect(Target, Range("E11,H11,E482,H482,E953,H953........")) Is Nothing code. My question is this: I can put all the 200 plus cells in the Range("E11,H11,E482,H482,E953,H953........") but then it would be a very, very long line. Is there way to go on a second and third or fourth line so that I could not loose track of all the cells I have to put in?

Thanks

 

[footoo]

https://www.exceltip.com/tips/how-d...-more-lines-in-microsoft-excel-2010-2013.html

 

[Norie]

First check the Target is in column E or H and then check it's row is in one you want to check.

If (Target.Column = 5 Or Target.Column = 8) And Target.Column Mod 471 = 11 Then
    CheckForSheet
    CalendarShow
Else
    CheckForSheet
    CalendarHide
End If

 

[pincivma]

Hi Norie

I understand the code If (Target.Column = 5 Or Target.Column = 8) since this refers to the columns but I don't understand the code Target.Column Mod 471 = 11. What does that mean? The rows that the pop-up calendar goes in varies. The rows are 11, 482, 953, 1424, 1895,2356... all the way up to 11012.

 

[Norie]

I thought I saw a pattern, starting row 11 and move 471 rows each time.

If there isn't a pattern like that then you are resigned to writing out the entire range, unless there's some other criteria you can use to determine whether or not to show the calendar.

 

[pincivma]

Hey Nori you know what? There is a pattern. I did not realize it. Thanks for pointing it out to me. I don't know if the pattern of moving 471 persists all the way down to the end but at least it is a start.
Thanks

 

[pincivma]

Hi again Nori

I tried your code this morning and it does not work. The calendar does not pop-up when I click on the cells. The calendar does pop up when I revert back to my code. I wonder why that is. Anyway, worse case scenario is that I have to revert back to my code. I have to figure out a way to put that long code into several lines instead of one long line.

 

[RoryA]

It should be Target.Row Mod 471 = 11

 

[Norie]

OOPS!!!

 

[pincivma]

Hi RoryA

Thanks for fixing the code. The calendars now pop-up up to row 4721. Then the pattern changes to every 150 rows for quite a bit and then to 190 rows. So the pattern is not consistent. Is there a way to incorporate the row changes into Nori's code or do I have to revert back to my old code and hope for the best.