Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'code to update a chart based on selection in a table of results
'if multiple cells selected it will use the top left value
'written by David Coutts 25/02/2004
    Dim isect           As Range
    Dim LastCol         As Integer
    Dim NewSeries       As String
    Dim NewSeriesName   As String
    
    'get the lastcolumn
    LastCol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    
    'define variables in case of need
    NewSeries = "='" & Me.Name & "'!R" & Target.Row & "C4:R" & _
                    Target.Row & "C" & LastCol - 1
    NewSeriesName = "='" & Me.Name & "'!R" & Target.Row & "C1:R" & Target.Row & "C2"
    
    
    
    'difference in activity(Table located A6:B531) should not change by definition\
    Set isect = Intersect(Target, Range("A6:B531"))
    'check if selected cell is in the target range
     If Not isect Is Nothing Then
        'alter the chart on the worksheet with the new chart
        'of the updated cells
        With Me.ChartObjects(1).Chart
            'ensure x-axis is of the correct format
            .Axes(xlCategory).TickLabels.NumberFormat = "mmm-yy"
            With .SeriesCollection(1)
                .Values = NewSeries
                '*******************************************************************
                .Name = NewSeriesName 'obtaining error 1004 on this line on occasion
                                    ' With error cannot define name of series collection
                '*******************************************************************
                ' if chart activity based colour blue
                .Interior.ColorIndex = 5
            End With
            'update the chart title
            .ChartTitle.Text = Cells(Target.Row, 1).Value & " " & _
                                Cells(Target.Row, 2).Value & " Difference in Values"
        End With
    End If
    
    
    'difference in costs(Table located A537:B1062) should not change by definition
    Set isect = Intersect(Target, Range("A537:B1062"))
    If Not isect Is Nothing Then
        With Me.ChartObjects(1).Chart
            'ensure x-axis is of the correct format
            .Axes(xlCategory).TickLabels.NumberFormat = "mmm-yy"
            With .SeriesCollection(1)
                .Values = NewSeries
                .Name = NewSeriesName
                'if the chart cost based then colour Brown
                .Interior.ColorIndex = 9
            End With
            'update the title of the chart
            .ChartTitle.Text = Cells(Target.Row, 1).Value & " " & _
                                Cells(Target.Row, 2).Value & " Difference in costs"
        End With
    End If
    
    'get rid of object variables
    Set isect = Nothing
End Sub

SELECT DISTINCT HSMOA, PDSEX
FROM Provider_APC_0405
ORDER BY HSMOA ASC, PDSEX asc