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

The following article shows you how to highlight a single bar or column:

Highlight a bar in a chart

This post demonstrates how to quickly bring attention to a single bar in a chart. Highlighted bars in two or […]

Comments(4) Filed in category: Charts, Excel, VBA

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

The following article shows you how to highlight column in a stacked column chart:

Highlight a column in a stacked column chart

I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]

Comments(2) Filed in category: Charts, Excel, Highlight

Download excel *.xlsm file

Color a stacked bar chart.xlsm

Back to top