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!


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