How to Hilight Chart's Source Data?

[ryguy7272]

iI am wondering if there is a way to use code to find cells that a chart is
linked to. For instance, if I right-click on a chart, and click on Source
Data, I can then see the cells that the chart is linked to in the ‘Data
Range’ or ‘Series’ tabs. Can I use a macro to automatically color the cells
that feed that chart. I would envision clicking on a chart and running the
code.

Essentially:
ActiveChart.ChartArea.Select
Hilight the cells that are selected...

So, I would just highlight the cells in this range. That way, I can easily
see the chart’s source data. Is that possible?

Thanks,
Ryan--

 

[Peter T]

This is crude, I'm ashamed to post it, but you might like it !

Sub test()
Dim sf As String
Dim arr, v
Dim rng As Range, rAll As Range
Dim cht As Chart
Dim sr As Series

Set cht = ActiveChart
If cht Is Nothing Then
MsgBox "Select a chart"
Exit Sub
End If

On Error Resume Next
For Each sr In cht.SeriesCollection
sf = sr.Formula
sf = Mid$(sf, 9, Len(sf) - 9) ' strip =SERIES( )
arr = Split(sf, ",")
For Each v In arr
Set rng = Range(v)
If Not rng Is Nothing Then

If rAll Is Nothing Then
Set rAll = rng
Else
Set rAll = Union(rAll, rng)
End If
Set rng = Nothing
End If
Next
Next

If Not rAll Is Nothing Then
Application.Goto rAll
End If

End Sub

Regards,
Peter T

 

[egun]

Here is a simple example that worked for me on a scatter chart with four
series, all separate regions on the sheet. It probably needs work to do what
you want, including bullet proofing and extension to other sheets or
workbooks, which I didn't check. If you can link it to selecting the chart
somehow, then you can also create an "undo" method that links to de-selection
of the chart and returns the cells back to their original formatting. This
would require you to save the original formatting of the cells somehow, a
problem I'll let you figure out!

HTH,

Eric

'
' This macro puts a bright red border around each range
' of data used in a chart
'
Sub Highlight_Chart_Cells()
Dim i As Long, j As Long
Dim thisChart As Chart
Dim Range1 As Range, Range2 As Range, Range3 As Range
'
Set thisChart = ActiveChart
For i = 1 To thisChart.SeriesCollection.Count
Call Split_Series_Ranges(thisChart.SeriesCollection(i).Formula,
Range1, Range2, Range3)
'
' If a range exists, highlight it with a bright red, thick border
'
If (Not Range1 Is Nothing) Then
For j = 1 To Range1.Areas.Count ' Highlight each area
separately...
With Range1.Areas(j)
.BorderAround xlSolid, xlThick, 3
End With
Next j
End If
'
If (Not Range2 Is Nothing) Then
For j = 1 To Range2.Areas.Count ' Highlight each area
separately...
With Range2.Areas(j)
.BorderAround xlSolid, xlThick, 3
End With
Next j
End If
'
If (Not Range3 Is Nothing) Then
For j = 1 To Range3.Areas.Count ' Highlight each area
separately...
With Range3.Areas(j)
.BorderAround xlSolid, xlThick, 3
End With
Next j
End If
'
Next i
'
End Sub
'
' This subroutine takes as input the formula for one series on a
' chart and returns as output the three ranges (if they all exist)
' that make up the series: the title, the x-values and the y-values.
'
Sub Split_Series_Ranges(inFormula As String, Range1 As Range, Range2 As
Range, Range3 As Range)
Dim i As Integer, j As Integer
Dim tStr As String
'
i = InStr(1, inFormula, "(")
j = InStr(1, inFormula, ",")
If (j - i > 1) Then ' Title range exists
Set Range1 = Range(Mid(inFormula, i + 1, j - i - 1))
Else
Set Range1 = Nothing
End If
'
i = j
j = InStr(i + 1, inFormula, ",")
If (j - i > 1) Then ' Category range exists
Set Range2 = Range(Mid(inFormula, i + 1, j - i - 1))
Else
Set Range2 = Nothing
End If
'
i = j
j = InStr(i + 1, inFormula, ",")
If (j - i > 1) Then ' Value range exists
Set Range3 = Range(Mid(inFormula, i + 1, j - i - 1))
Else
Set Range3 = Nothing
End If
'
End Sub

 

[egun]

Chip Pearson's discussion of event handling in VBA discusses how to deal with
charts. He shows how to catch the activation event. Just look for the word
oddball!

http://www.cpearson.com/excel/Events.aspx

 

[ryguy7272]

Wow! Super cool! Thanks for sharing, Peter and egun!!
Ryan---