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?
Thanks!
alexb523
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 SubThanks!
alexb523
