VBA Chart by series color, update to Chart by cell color (stacked Bar chart)

[brandon20]

Hi,
I found this code to color a chart by a series color. I need to color my chart not by the series color, but by each cells color.

currently this seems to be where the series color is coming from, ie(if the Item 1 is red, then the every bar associated with this column is red (I am using a stacked bar chart)

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

I need every bar to by colored by its specific cell color, not just red
Is this a simple modification to this VBA? or would this be something different all together.
Any help is appreciated. or link to a VBA code that can already do this.
Thanks!



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

 

[brandon20]

http://www.mrexcel.com/forum/excel-...colour-represented-within-bar-chart-rows.html

this link is my exact question. It has been answered.

Thank you Andrew.