Table of contents

  1. Format fill color on a column chart based on cell color
  2. Change stacked bar colors


Mark Graveson asks:

A little off topic question, but I am thinking outside the box a little.

I have two charts that display different data sets for the same projects. I want the formatting of the series (projects) to be the same on each chart so they are recognizable  Could similar VBA code be used to format the fill colour on the charts based on how you colour the series names in the data tables?

format the fill colour on a chart based on cell color

(Animated gif)

VBA code

Sub ColorChartColumnsbyCellColor()
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1)
Set vAddress = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))
For i = 1 To vAddress.Cells.Count
.Points(i).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(vAddress.Cells(i).Interior.ColorIndex)
Next i
End With
End Sub

Download excel *.xlsm file

Color chart columns by cell color.xlsm

Back to top

Change stacked bar colors

The following picture shows you a stacked bar chart, the macro below lets you color the bars with the same color as the source range.

You simply select the stacked bar chart you want to color differently. Make sure you have colored the source cell range. Go to "Developer" tab on the ribbon, click "Macros" button. Select "ColorChartBarsbyCellColor" and click OK.

Series 1 (Asia) has it's source values in cell range B2:B5. The color in that cell range matches the color in the stacked bar chart.

VBA

Sub ColorChartBarsbyCellColor()
Dim txt As String, i As Integer
c = ActiveChart.SeriesCollection.Count
For i = 1 To c
txt = ActiveChart.SeriesCollection(i).Formula
arr = Split(txt, ",")
With ActiveChart.Legend.LegendEntries(i)
Set vAddress = ActiveSheet.Range(arr(2))
.LegendKey.Interior.Color = ThisWorkbook.Colors(vAddress.Cells(1).Interior.ColorIndex)
End With
Next i
End Sub

Download excel *.xlsm file

Color a stacked bar chart.xlsm

Back to top