Hello all,

I was wondering if it is possible to loop through data points in a chart and change their color based upon a corresponding cell. For example: all my charted data is in column C but it is separated into categories (juice, pop, candy, fruit) in column A... What I'd like to do is, if the number corresponds to juice, for the point to be yellow, if it corresponds to pop to be green, candy = orange, fruit = blue. I found a macro to change the color of data points to the color of the source cell but, it only works for individual charts for each of the categories... I'd like it to work for one chart containing all the categories. I've pasted the macro below, please let me know if you have any questions.

thanks.
**MySeries is used in place of myPoint for individual chart coloring
Sub CellColorsToPoints()
Dim oChart As ChartObject
Dim MySeries As Series
Dim myPoint As Point
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 myPoint In oChart.Chart.SeriesCollection(1).Points 'This ends at SeriesCollection for individual charts
        
        'Get Source Data Range for the target series
        FormulaSplit = Split(MyPoint.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 2007 and 2010
        myPoint.MarkerBackgroundColor = SourceRangeColor
        myPoint.MarkerForegroundColor = SourceRangeColor
        myPoint.Format.Line.ForeColor.RGB = SourceRangeColor
        myPoint.Format.Line.BackColor.RGB = SourceRangeColor
        myPoint.Format.Fill.ForeColor.RGB = SourceRangeColor
   
     Next myPoint
Next oChart

End Sub