Solved: Workbook_SheetChange and If Not Intersect error

[greglittle]

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!


[VBA]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not Intersect(Range("Country"), Target) Is Nothing Then 'Updates the curve chart on the risk sheet with the correct countries
Dim Abrv As String
'Dim RefCtry As Variant
'Dim US As Variant
'Dim ProjCtry As Variant
Abrv = Application.WorksheetFunction.VLookup(Range("Country"), Range("CountryTable"), 2, False)

Set US = Sheet1.ChartObjects("CtryChart").Chart.SeriesCollection(3)
Set RefCtry = Sheet1.ChartObjects("CtryChart").Chart.SeriesCollection(2)
Set ProCtry = Sheet1.ChartObjects("CtryChart").Chart.SeriesCollection(4)

Select Case Range("Country").Value
Case "United States"
US.Points(1).DataLabel.Font.ColorIndex = 3 'Make US Red
RefCtry.Points(1).DataLabel.Font.ColorIndex = 1 'Make Ref Countries Black
ProCtry.Points(1).HasDataLabel = False 'Remove ProCountry label

Case "Brazil"
US.Points(1).DataLabel.Font.ColorIndex = 1 'Make US Black
RefCtry.Points(1).DataLabel.Font.ColorIndex = 1 'Make Ref Countries Black
ProCtry.Points(1).HasDataLabel = False 'Remove ProCountry label

RefCtry.Points(1).DataLabel.Font.ColorIndex = 3 'Make Brazil Red

Case Else

End Select
End If

End Sub[/VBA]

[greglittle]

Decided to put it in a sub, instead, that runs when the country value is changed in the UI. This actually saved calculation time.