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

[Moondrift]

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 TechonologiesNQC Dashboard xID.xlsm) 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?