Edit to "colour to chart" macro - to cycle through workbook

Hi All,

[Third time I have written this post - so my apologies if I miss anything!]

I have been using a macro recently to colour up graphs based on client detail (c/o DataPig TechonologiesAttachment 21599) and it works really nicely on individual sheets. The macro is as follows:

Code:

---


Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor As Long
Dim NumberofDataPoints As Long
Dim iPoint As Long

'Loop through all charts in the active sheet
For Each oChart In ActiveSheet.ChartObjects

'Loop through all series in the target chart
For Each MySeries In oChart.Chart.SeriesCollection

NumberofDataPoints = MySeries.Points.Count

For iPoint = 1 To NumberofDataPoints

'Get Source Data Range for the target series
FormulaSplit = Split(MySeries.Formula, ",")

'Capture the first cell in the source range then trap the color
Set SourceRange = Range(FormulaSplit(2)).Item(iPoint)
SourceRangeColor = SourceRange.DisplayFormat.Interior.Color

On Error Resume Next
'Coloring for Excel 2003
'MySeries.Interior.Color = SourceRangeColor
' MySeries.MarkerBackgroundColorIndex = SourceRangeColor
' MySeries.MarkerForegroundColorIndex = SourceRangeColor

'Coloring for Excel 2007 and 2010
MySeries.Points(iPoint).MarkerBackgroundColor = SourceRangeColor
MySeries.Points(iPoint).MarkerForegroundColor = SourceRangeColor
MySeries.Points(iPoint).Format.Fill.ForeColor.RGB = SourceRangeColor

Next
Next MySeries
Next oChart

End Sub

---

I now have a workbook consisting of two sheets: a data sheet (tabulated) and a dashboard show date and test selections and a plot based on these. What I would like is for the macro to be able to colour the plotted data points on sheet 2 based on the conditional formatting on sheet 1 (example attached). Can anyone help me with this please?

Hi Moondrift and Welcome to this forum. You can trial this. HTH. Dave

Code:

---

Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor As Long
Dim NumberofDataPoints As Long
Dim iPoint As Long

'Loop through all charts in the active sheet
For Each oChart In Sheets("Sheet2").ChartObjects
 'Loop through all series in the target chart
 For Each MySeries In oChart.Chart.SeriesCollection
 NumberofDataPoints = MySeries.Points.Count
 For iPoint = 1 To NumberofDataPoints
 'Get Source Data Range for the target series
 FormulaSplit = Split(MySeries.Formula, ",")
 'Capture the first cell in the source range then trap the color
 Set SourceRange = Sheets("Sheet1").Range(FormulaSplit(2)).Item(iPoint)
 SourceRangeColor = SourceRange.DisplayFormat.Interior.Color
 On Error Resume Next
 'Coloring for Excel 2003
 'MySeries.Interior.Color = SourceRangeColor
 ' MySeries.MarkerBackgroundColorIndex = SourceRangeColor
 ' MySeries.MarkerForegroundColorIndex = SourceRangeColor
 'Coloring for Excel 2007 and 2010
 MySeries.Points(iPoint).MarkerBackgroundColor = SourceRangeColor
 MySeries.Points(iPoint).MarkerForegroundColor = SourceRangeColor
 MySeries.Points(iPoint).Format.Fill.ForeColor.RGB = SourceRangeColor
 Next
 Next MySeries
Next oChart
End Sub

---

ps. please use code tags

At its simplest from a coding point of view you could try something like:

Code:

---

Sub blah()
  For Each sht In Sheets
    sht.Activate
    CellColorsToChart
  Next sht
End Sub

---

Hi Fellas,

Sorry it has taken me so long to acknowledge your emails - I have been away from work, unwell.

Unfortunately we haven't had a lot of luck with the getting the macro to work. It seems that the it does actually run, but the way that the pivot table selects the datapoints for plotting doesn't seem to lock the conditional formatting to the point being plotted (if that makes sense?!).

We have managed a slightly cumbersome work around... By adding a third page to the workbook, so that we have "raw data", "selected data" and the "table/graph page". So I now have to run the macro to get the graph to update at all, but a least it is showing all the data with the correct colouration now.

If you have any further suggestions, I'd love to hear from you.

My apologies again taking so long to reply.

Many thanks, MD.

Can you supply a file with this sort of stuff in it? It will save us having to do it and prevent us guessing wrongly how your charts are set up.

I thought I had added it last time, but I must have missed it somehow..?

Cheers, M.

Give me a couple of days (travelling).
Others might jump in.

Test thoroughly:

Code:

---

Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range, cll As Range
Dim SourceRangeColor As Long
Dim i As Long
 
'Loop through all charts in the active sheet
For Each oChart In ActiveSheet.ChartObjects
  'Loop through all series in the target chart
  For Each MySeries In oChart.Chart.SeriesCollection
    'Get Source Data Range for the target series
    FormulaSplit = Split(MySeries.Formula, ",")
    Set SourceRange = Range(FormulaSplit(2)).SpecialCells(xlCellTypeVisible)
    i = 1
    'On Error Resume Next 'avoid if possible.
    For Each cll In SourceRange.Cells
      SourceRangeColor = cll.DisplayFormat.Interior.Color
      With MySeries.Points(i)
        .MarkerBackgroundColor = SourceRangeColor
        .MarkerForegroundColor = SourceRangeColor
        .Format.Fill.ForeColor.RGB = SourceRangeColor
      End With
      i = i + 1
    Next cll
    'On Error GoTo 0 'use only if you use On Error Resume Next above.
  Next MySeries
Next oChart
End Sub

---

Well, it's getting better. It is colouring the 'chunks' based on the colours selected, but it's almost like it is not recognising the dates in the associated cells?

I may be asking way too much, so feel free to tell me it can't be done an move onto something more interesting ;)

Cheers, M.

Quote:

---

Originally Posted by Moondrift

but it's almost like it is not recognising the dates in the associated cells?

---

Can you be more specific? What's not happenning that should be?