Author: Oscar Cronquist Article last updated on September 10, 2020

Color columns in chart based on cell color

This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is based on a regular column chart and the second example shows a stacked bar chart.

The image above shows a data table in cell range A1:B6 containing continents in the world and a random number.

The chart graphs the regions and numbers using columns, categories are distributed horizontally (x-axis) and numbers vertically (y-axis) from 0 to 45 with increments of 5.

To the right of the data table is a button linked to a VBA macro. When you press the button named "Color chart columns" macro ColorChartColumnsbyCellColor is executed.

The VBA macro goes through each cell in the chart data source, copies the cell color and applies the same color to the corresponding chart column.

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?

VBA code

'Name macro
Sub ColorChartColumnsbyCellColor()

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Sheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection(1)

'Save chart data source range from first chart on worksheet Sheet1
Set vAddress = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))

'Iterate through each cell in data source range
For i = 1 To vAddress.Cells.Count

'Copy color from cell to bar
.Points(i).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(vAddress.Cells(i).Interior.ColorIndex)

'Continue with next cell
Next i
End With
End Sub

Where to put the code?

Color columns in chart based on cell color VB Editor 1

The VB Editor allows you to build macros and UDFs in Excel. To the left is the "Project Explorer" window and to the right is a window where you put your code.

The Project Explorer window lets you choose which open workbook to use and if you want to save your macro in a worksheet module or a regular module.

  1. Copy VBA code.
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. Click on "Insert" on the top menu.
  4. Click on "Module", the module name appears below your workbook in the "Project Explorer" window.
  5. Paste VBA code to window, see image above.
  6. Exit VB Editor and return to Excel.
Note, save your workbook with file extension *.xlsm (macro-enabled workbook) to keep the code.

How to create and link a button?

Color columns in chart based on cell color button
  1. Go to tab "Developer" on the ribbon.
  2. Click on the "Insert" button on the ribbon.
  3. Click on "Button".
  4. Click and hold with left mouse button on your worksheet.
  5. Drag with mouse until you got the size you want.
  6. Release left mouse button.
  7. A dialog box appears allowing you to assign a macro.
  8. Click macro "ColorChartColumnsbyCellColor" to select it.
  9. Click "OK" button.

You can now click the button to trigger the macro.

Back to top

Change stacked bar colors programmatically

The picture above shows a stacked bar chart and a data table with colored columns, each category has it's own color based on the corresponding data table column.

The macro below lets you color the bars with the same color as the source range.

How to use macro

You 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

'Name macro
Sub ColorChartBarsbyCellColor()

'Dimension variables and declare data types
Dim txt As String, i As Integer

'Save the number of chart series to variable c
c = ActiveChart.SeriesCollection.Count

'Iterate through chart series
For i = 1 To c

'Save seriescollection formula to variable txt
txt = ActiveChart.SeriesCollection(i).Formula

'Split string save d to txt using a comma ","
arr = Split(txt, ",")

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveChart.Legend.LegendEntries(i)

'The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object.
'Save a range object based on variable arr to variable vAdress
Set vAddress = ActiveSheet.Range(arr(2))

'Copy cell color from cell and use it to color bar chart
.LegendKey.Interior.Color = ThisWorkbook.Colors(vAddress.Cells(1).Interior.ColorIndex)
End With

'Continue with next series
Next i
End Sub

Where to put the code?

Download Excel file


Color-a-stacked-bar-chart.xlsm

Back to top