VBA Chart Matches Cell color, but Legend Won't Follow

[alexb523]

Hello,

I am trying to change all my charts to be the same color as the assigned cell and then catch and conditional formatting and change that specific bar to be that color.
I have been successful so far, as there are many similar codes out there to the one i used.

Below is the code. But how can i get the legend to also change colors?

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
Dim ws As Worksheet


'activate each worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate


'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(1)
        SourceRangeColor = SourceRange.Interior.Color
            'capture the conditional 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
         MySeries.Points(iPoint).MarkerBackgroundColor = SourceRangeColor
           MySeries.Points(iPoint).MarkerForegroundColor = SourceRangeColor
           MySeries.Points(iPoint).Format.Line.ForeColor.RGB = SourceRangeColor
           MySeries.Points(iPoint).Format.Line.BackColor.RGB = SourceRangeColor
           MySeries.Points(iPoint).Format.Fill.ForeColor.RGB = SourceRangeColor
       
        Next
    Next MySeries
Next oChart
Next ws


End Sub

Thanks!
alexb523

 

[Domenic]

Try setting the colors by series, instead of by points. Maybe something like this...

[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CellColorsToChart()

    [color=darkblue]Dim[/color] Wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] oChartObj [color=darkblue]As[/color] ChartObject
    [color=darkblue]Dim[/color] oSeries [color=darkblue]As[/color] Series
    [color=darkblue]Dim[/color] sFormula [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] rSourceCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] SourceColor [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Wks [color=darkblue]In[/color] Worksheets
        [color=darkblue]For[/color] [color=darkblue]Each[/color] oChartObj [color=darkblue]In[/color] Wks.ChartObjects
            [color=darkblue]With[/color] oChartObj.Chart
                [color=darkblue]For[/color] [color=darkblue]Each[/color] oSeries [color=darkblue]In[/color] .SeriesCollection
                    sFormula = Split(oSeries.Formula, ",")(2)
                    [color=darkblue]Set[/color] rSourceCell = Range(sFormula).Cells(1)
                    SourceColor = rSourceCell.Interior.Color
                    [color=darkblue]With[/color] oSeries
                        .Format.Fill.ForeColor.RGB = SourceColor
                        .Format.Line.ForeColor.RGB = SourceColor
                        .Format.Line.BackColor.RGB = SourceColor
                        .MarkerForegroundColor = SourceColor
                        .MarkerBackgroundColor = SourceColor
                    [color=darkblue]End[/color] [color=darkblue]With[/color]
                [color=darkblue]Next[/color] oSeries
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]Next[/color] oChartObj
    [color=darkblue]Next[/color] Wks
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!

 

[alexb523]

Hi Domenic,

There seems to be a bug in the line ".MarkerForegroundColor = SourceColor"

What could be the issue there?

Thanks,
alexb523

 

[alexb523]

Hi Domenic,

I commented out .MarkerForegroundColor = SourceColor and .MarkerBackgroundColor = SourceColor and it seemed to work.

is the markers for a specific type of graph?

thanks,
alexb523

 

[Domenic]

Yes. Only some charts have that property. What type of charts do you have?

 

[alexb523]

Hi Domenic,

Sorry to reply so much.

I think i know what you are trying to say with changing the series instead of the just the individual points. But i need to capture if there is conditional formatting in the cell. Is there a way to capture the series to change the legend and the go back to doing it by points to pick up any conditional formatting?

Thanks,
alexb523

 

[alexb523]

i have column charts and line graphs, and then some combo column and line graphs.

 

[Domenic]

Okay, I've change it so that it takes into consideration conditional formatting, and it applies the formatting for markers only to any series which has one of the line chart types (remove line chart types as desired)...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] CellColorsToChart()

    [COLOR=darkblue]Dim[/COLOR] Wks [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] oChartObj [COLOR=darkblue]As[/COLOR] ChartObject
    [COLOR=darkblue]Dim[/COLOR] oSeries [COLOR=darkblue]As[/COLOR] Series
    [COLOR=darkblue]Dim[/COLOR] sFormula [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] rSourceCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] SourceColor [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Wks [COLOR=darkblue]In[/COLOR] Worksheets
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oChartObj [COLOR=darkblue]In[/COLOR] Wks.ChartObjects
            [COLOR=darkblue]With[/COLOR] oChartObj.Chart
                [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oSeries [COLOR=darkblue]In[/COLOR] .SeriesCollection
                    sFormula = Split(oSeries.Formula, ",")(2)
                    [COLOR=darkblue]Set[/COLOR] rSourceCell = Range(sFormula).Cells(1)
                    SourceColor = rSourceCell.DisplayFormat.Interior.Color
                    [COLOR=darkblue]With[/COLOR] oSeries
                        .Format.Fill.ForeColor.RGB = SourceColor
                        .Format.Line.ForeColor.RGB = SourceColor
                        .Format.Line.BackColor.RGB = SourceColor
                        [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] .ChartType
                            [COLOR=darkblue]Case[/COLOR] xlLine, xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100, xlLineStacked, xlLineStacked100
                                .MarkerForegroundColor = SourceColor
                                .MarkerBackgroundColor = SourceColor
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]Next[/COLOR] oSeries
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Next[/COLOR] oChartObj
    [COLOR=darkblue]Next[/COLOR] Wks
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[alexb523]

Hi Domenic,

I appreciate your help. I am not very good at VBA but hoping to improve my skills and understanding.

It seems like the code you posted takes into account the color of the first cell in the series, regardless of whether it is formatted differently according to my conditional format rule or not.
Which, from my understanding, picking up the conditional formatting is the hardest part because it is not actually in the cell or something like that.

Could the like Set rSourceCell = Range(sFormula).Cells(1) have to do with it only picking up the color in the first cell?

Thanks,
alexb523

 

[Domenic]

Okay, I'm a little confused. If you want to format each marker according to its corresponding cell then, yes, you'll have to loop through each point. But where should the formatting for each series come from? Can you please clarify?