Workbook_SheetChange and If Not Intersect error

**greglittle** · 10-25-2012, 10:37 AM

Hi. I keep getting run-time error 1004 (Method 'Intersect' of object '_Global' failed) when I enter a value in a cell other than the intersect range. The code works just fine when the intersect range is changed, but trips up when I change a cell in some sheets (but not all sheets). It's very strange. Is there something I'm missing here? The goal is to update formatting of a point on a chart depending on which country is selected.

I can't use the Worksheet_Change event because the country can be changed from the UI, so I need the chart to update even when the user is not on the chart's worksheet.

Thanks for your help!

Please Login or Register  to view this content.

**JosephP** · 10-25-2012, 10:45 AM

which sheet is the country range on? you need to test if Sh is that sheet before you try and use intersect because you can't use intersect with ranges on different sheets

**JosephP** · 10-25-2012, 10:48 AM

perhaps

Please Login or Register  to view this content.

**greglittle** · 10-25-2012, 11:19 AM

Joseph,

That worked, thank you. Now though, every time I change any cell on any sheet Excel will pause for a couple seconds of calculation time. I don't know why I didn't think of this before, but since the user is inputting country on the UI, I can just put all of this code in a sub that runs when the user changes the country. (doh!)

P.S. I used to live just south of Sandy back in the day... loved riding Brighton and Canyons. I hope you guys get some good snow this winter! Take care.