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 (Sheets("FR").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("C9:R40, C47:R78"), 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