Elegant way to highlight chart data series in Excel

Question

I want to outline the chart data range source(s) in a table, in much the same way that the GUI will outline a range in blue if the chart data series is clicked. The user can choose various chart views and the range highlight colours for each data series need to match those displayed in the chart.

For the record, here are the methods I considered:

1. Parse the chart series values string and extract the data range
2. Do a lookup on a table that stores information on the ranges and the colours to be used

In the end I went with option 2 as is seemed easier to implement and to properly manage the colours I would probably have to store them for method 1 anyway, negating its benefits.

The highlight procedure is called from the Worksheet_Change event, a lookup is done on the chart name, the ranges and colours pulled from the table and then the cell formatting is carried out. The limitation of this method is that the range/colour data for each new chart view must be pre-calculated. This isn't much of a problem for my current implementation, but my be a limiting factor in future use where the charts might be more dynamic.

So although I've got a version of this working fine, I'm sure there must be a more elegant way of achieving this.

Any suggestions?

Answer 1

Have you tried using Conditional Formatting?

Answer 2

Edit:

OK, this seems to handle more cases better. The triggering code is the same, but here is new code for the module:

Function SeriesRange(s As Series) As Range
    Dim sf As String, fa() As String


    sf = s.Formula
    sf = Replace(sf, "=SERIES(", "")

    If sf = "" Then
        Set SeriesRange = Nothing
        Exit Function
    End If

    fa = Split(sf, ",")


    Set SeriesRange = Range(fa(2))

End Function

Sub x(c As Chart)
    Dim sc As Series
    Dim sr As Range

    If SeriesRange(c.SeriesCollection(1)) Is Nothing Then
        Exit Sub
    End If

    Set sr = SeriesRange(c.SeriesCollection(1))

    sr.CurrentRegion.Interior.ColorIndex = xlNone
    For Each sc In c.SeriesCollection
        If sc.Interior.Color > 1 Then
            SeriesRange(sc).Interior.Color = sc.Interior.Color
        ElseIf sc.Border.ColorIndex > 1 Then
            SeriesRange(sc).Interior.Color = sc.Border.Color
        ElseIf sc.MarkerBackgroundColorIndex > 1 And sc.MarkerBackgroundColorIndex < 57 Then
            SeriesRange(sc).Interior.ColorIndex = sc.MarkerBackgroundColorIndex
        ElseIf sc.MarkerForegroundColorIndex > 1 And sc.MarkerForegroundColorIndex < 57 Then
            SeriesRange(sc).Interior.ColorIndex = sc.MarkerForegroundColorIndex
        Else
            MsgBox "Unable to determine chart color for data series " & sc.Name & " ." & vbCrLf _
                    & "It may help to assign a color rather than allowing AutoColor to assign one."
        End If
    Next sc

End Sub

/Edit

This is probably more barbaric than elegant, but I think it does what you want. It involves your first bullet point to get the range from the Series object, along with a sub to run through all the Series objects in the SeriesCollection for the chart. This is activated on Chart_DeActivate. Most of this code is jacked - see comments for sources.

In a module:

Function SeriesRange(s As Series) As Range
    Dim sf As String, fa() As String
    Dim i As Integer
    Dim result As Range

    sf = s.Formula
    sf = Replace(sf, "=SERIES(", "")

    fa = Split(sf, ",")

    Set SeriesRange = Range(fa(2))
End Function

Sub x(c As Chart)
    Dim sc As Series
    Dim sr As Range

    Set sr = SeriesRange(c.SeriesCollection(1))

    sr.CurrentRegion.Interior.ColorIndex = xlNone

    For Each sc In c.SeriesCollection
        SeriesRange(sc).Interior.Color = sc.Interior.Color
    Next sc

End Sub

In the ThisWorkbook object module:

' Jacked from C Pearson http://www.cpearson.com/excel/Events.aspx '
Public WithEvents CHT As Chart

Private Sub CHT_Deactivate()
    x CHT 
End Sub

Private Sub Workbook_Open()
    Set CHT = Worksheets(1).ChartObjects(1).Chart 
End Sub