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

alexb523

Board Regular
Joined
Dec 12, 2013
Messages
115
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?

Code:
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
 
Thanks for you help Domenic.

Please look out for my next post as I am trying to add a new Tab then Paste all my graphs to that Tab.

-alexb523
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,214,987
Messages
6,122,614
Members
449,091
Latest member
gaurav_7829

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top