Intersection - Multiple ranges SUDDENLY not supported

[Geronim0]

Hello all,

As a starting user of VBA in Excel I have frequently used this forum for solutions to my vba problems (many thanks for that!). This time I couldn't find an answer to my problem, so I decided to register and ask for myself.
I wrote a sub that is fired by the Worksheet_SelectionChange event. I put it in the code page for the Worksheet ("FR") itself. Cut to the bone it looks like this:

Private Sub Worksheet_SelectionChange (ByVal Target As Range)
 If Not Intersect (Range("C9:R40, C47:R78"), Target) Is Nothing Then
 '(irrelevant code for this problem)
End if
End Sub

This worked perfect, until yesterday. Now I get the error 1004 - method range of object_worksheet has failed (translation of the error is mine, since I have a Dutch Office version).

I changed the code to:

Private Sub Worksheet_SelectionChange (ByVal Target As Range)
 If Not Intersect ([B]Sheets("FR")[/B].Range("C9:R40, C47:R78"), Target) Is Nothing Then
 '(irrelevant code for this problem)
End if
End Sub

It still results in an error: 1004 - by application or object defined error
Then I found out that the resulting problem is in the fact that I defined multiple ranges.

So when the code is changed to the following, it works:

Private Sub Worksheet_SelectionChange (ByVal Target As Range)
 If Not Intersect (Sheets("FR").Range([B]"C9:R40"[/B]), Target) Is Nothing Then
 '(irrelevant code for this problem)
End if
End Sub

So it works for a single range but I need to refer to multiple ranges (even more than in the above mentioned example. I already tried to use the names I specified for the ranges in stead of eg C9:R40, but the error remains.

The question is:

- How come I get these 2 errors now and not before yesterday?
- What can I do about it?

Help is much appreciated!

Kind regards,

Geronimo

 

[GTO]

Not tested, but I believe you want the ranges as different args in Intersect:

If Not Application.Intersect(Range("C9:R40"), Range("C47:R78"), Target) Is Nothing Then

 

[Geronim0]

Not tested, but I believe you want the ranges as different args in Intersect:

If Not Application.Intersect(Range("C9:R40"), Range("C47:R78"), Target) Is Nothing Then

I first tried your suggestion, it didn't cause the error but didn't fire the event either. But...

I discovered what went wrong! There were two versions of this excel file open, both with the same worksheet names, range names etc. Closing the other version was the solution.

Thanks for you time and effort, problem solved!