first timer using VB to cut and paste macro - getting compile error syntax error

[jopedder]

Hi everyone,

I've been trying to use either of the macros here or here, but am struggling

I worked out how to get to VB, insert a module and paste the code (having initially kept the /VB tags), but I get the error "Complile Error Syntax Error"

it is probably something really basic, but I would really appreciate your help.

[Olly]

Paste the actual code you are trying to use on here, in [ code ] tags.

[jopedder]

thanks OllyXLS, here it is:

Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor 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, “,”)

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

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

‘Coloring for Excel 2007 and 2010
MySeries.MarkerBackgroundColor = SourceRangeColor
MySeries.MarkerForegroundColor = SourceRangeColor
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.BackColor.RGB = SourceRangeColor
MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor

Next MySeries
Next oChart

End Sub

[Olly]

Try replacing the quotation marks in line 15:

FormulaSplit = Split(MySeries.Formula, “,”)

as follows:

FormulaSplit = Split(MySeries.Formula, ",")

Do you see the very subtle difference?! There's one of the risks of pasting code from a website, which isn't rendered in a plain font. It's why we use [ code ] tags here.

[jopedder]

that did get me a little further, I then tried changing

‘Loop through all...

to

'Loop through all...

and the other instances of '.

I now get further, but I have the error runtime error'9': subscript out of range.

VB highlights this line in yellow:

Set SourceRange = Range(FormulaSplit(2)).Item(1)

I wonder if I should define something specific to my workbook? - the webpage implied I could just paste the code and go though?

thanks, Jo

[jopedder]

Solved - not by fixing this, but I found different code that worked (and did what I wanted more closely) - Thanks.


... just in-case someone searches using that code, they can find the one which works here.

It colours charts by the cell colour of the data, so you can easily choose the chart colour and maintain consistency.