PDA

View Full Version : Edit to "colour to chart" macro - to cycle through workbook



Moondrift
02-12-2018, 01:25 AM
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 Techonologies21599) and it works really nicely on individual sheets. The macro is as follows:


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?

Dave
02-12-2018, 06:21 AM
Hi Moondrift and Welcome to this forum. You can trial this. HTH. Dave

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

p45cal
02-12-2018, 09:01 AM
At its simplest from a coding point of view you could try something like:

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

Moondrift
03-05-2018, 03:36 PM
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.

p45cal
03-06-2018, 01:42 AM
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.

Moondrift
03-06-2018, 03:30 PM
I thought I had added it last time, but I must have missed it somehow..?

Cheers, M.

p45cal
03-06-2018, 04:17 PM
Give me a couple of days (travelling).
Others might jump in.

p45cal
03-09-2018, 09:33 AM
Test thoroughly:
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

Moondrift
03-12-2018, 07:50 PM
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.

p45cal
03-17-2018, 07:44 AM
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?