Color chart columns 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.
What's on this page
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 rund.
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.
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?
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.
- Copy VBA code.
- Press Alt + F11 to open the Visual Basic Editor.
- Press with mouse on "Insert" on the top menu.
- Press with mouse on "Module", the module name appears below your workbook in the "Project Explorer" window.
- Paste VBA code to window, see image above.
- Exit VB Editor and return to Excel.
How to create and link a button?
- Go to tab "Developer" on the ribbon.
- Press with mouse on the "Insert" button on the ribbon.
- Press with mouse on "Button".
- Press and hold with left mouse button on your worksheet.
- Drag with mouse until you got the size you want.
- Release left mouse button.
- A dialog box appears allowing you to assign a macro.
- Press with left mouse button on macro "ColorChartColumnsbyCellColor" to select it.
- Press with left mouse button on "OK" button.
You can now press with left mouse button on the button to trigger the macro.
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, press with left mouse button on "Macros" button. Select "ColorChartBarsbyCellColor" and press with left mouse button on 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
Charts category
Table of Contents How to add lines between stacked columns/bars (Excel charts) Use slicers to quickly filter chart data How […]
Table of Contents How to create an interactive Excel chart How to filter chart data How to build an interactive […]
What's on this page Custom data labels Improve your X Y Scatter Chart with custom data labels How to apply custom […]
Column chart category
This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally […]
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types […]
What is on this page? Built-in charts How to create a column chart How to create a stacked column chart […]
Excel categories
24 Responses to “Color chart columns based on cell color”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
Awesome Oscar, thanks so much!
Hi Oscar
Awesome solution.
I have a question: Do you know a solution for Fill Color of Column Chart with a match on cells with Conditional Format Colors. Example: Red for values 50. In this case each value is a part of One Series. Thanks for you reaction.
With Regards
Bert van Zandbergen, Beekbergen, The Netherlands
Bert van Zandbergen,
As far as I know, no.
Easy to understand an very reasonable for my jobs. The beginners among you can get more information on this site, which I use almost daily. https://www.excel-aid.com/excel-color-scale-customizing-the-color-scale-format-2.html
This is great thank you very much for sharing!!! Is there a way to make this cycle through all charts on the sheet?
Runtime error 1004.
Nice try
thanks alot this was really helpfull
amal aloun,
thank you for your comment.
Hey Thanks heaps Oscar.
I'm trying to build a stack plan for a property that formats the colour of each data point based on its lease expiry.
I'm wondering whether this code would work for a stacked bar chart with multiple series?
Cheers
Andrew
I made a new macro, it works with a stacked bar chart with multiple series, read this: https://www.get-digital-help.com/2013/09/14/format-fill-color-on-a-column-chart-based-on-cell-color/#bar
Be really good if it worked
Dan,
Is the attached file not working for you?
This works great. I am using the 2nd macro "ColorChartBarsbyCellColor()" and was wondering if there is a way to make it see conditional formatting I have created to color code data? If I format the colors via regular means it translates to the graph when I run the Macro, but when re-formatted with conditional formatting it does not pick that color up.
Basically I am taking time stamp data and creating a color-coded by event type timeline.
Hi Oscar,
Thank you for the VBA Code to colour the bar chart by cell colour (source).
It worked well for the first chart, and when I tried to run the same code on the second chart, it didn't work. I have 33 bar charts that would require me to apply the same colour format.
Can you help please as I have very limited knowledge of VBA coding. Thanks.
Thank you for your amazing tutorials! I'd like the bars on my chart to be colored based on either conditional formatting in my table, or unique text in my table (or their own labels which are based on a specified range in my table). Is there a tweak to your code to enable this?
Background info: I'm creating an hourly fitness class schedule in a column chart (similar to gantt). I've created a table which lists each person's name, class start time, class end time, class duration (auto calculated), and class name (with conditional formatting changing the cell color based on class name.)
My chart bar labels are deriving their values from my Class Name table column. But I'd like the bar colors to also change based on the conditional colors in my Class Name column. I'm new to excel vba/macros, but I tried your supplied code and there was an error because I don't have a legend/my legend references another column. Is there a way to make this work? Can a bar change colors based on label, or a specified table range w/conditional formatting? Any help is greatly appreciated!!
One other question!
Regarding the same chart, if one person is in a class at 3:30 for an hour, and then a different class at 4:30 for an hour... how can I show two bars in the same row, instead of the chart auto creating two rows for the same person? (Below is a general representation of the basic layout of my chart, and how I'd like two bars on one line/person)
3:30 4:30 5:30 6:30 7:30
Tom ________ ________
Dick ___________
Harry ________
Thank you!!!
Hi,
Thanks for sharing this!
While it changes colors based on cell colors its dose not give me the exact same color.
Example 1 - RED:
Red in my cell is RGB(0,176,80), while the color that appears in my graph is RGB(0,128,128)
Example 2 - GREEN:
Green in my cell is RGB(227,19,25), while the color that appears in my graph is RGB(255,0,0)
Excel is using a customized Color Theme. Could it be that VBA uses a different one? Or, how might I fix this?
Andreas,
try this macro:
Hi Oscar
This is great - though I am having the same trouble as Andreas and am unable to get the colours to match. I am a total beginner so would appreciate if you could give the whole code that I can replace the ColorChartBarsbyCellColor() Sub with. The one you posted in response to Andreas doesn't seem to work.
Thanks!
Hi, Oscar.
This code very usefull for 1 chart type, and I Have Question.
if i have more than 1 (ex. 3 chart) charts , can the code still be used?
Because i have 3 different/cathegory data.
So, Can the code still be used to changing color Bars for 3 chart in one time.
Thanks,
For chart #Color columns in chart based on cell color
thanks Oscar,
My requirement is bar color matching exactly same cell color. I have different color in each cell based on certain condition
How do i make a bar chart change colour between charts when i change the data series when i make selection from the drop down box - ie i select income and the chart shows bars jan to dec in blue, i select expenses and the chart bars jan to dec show in red. I select savings and the chart bars show from jan to dec in green?
Hello,
Thank you very much for your help and guidance.
I have a question regarding your post
https://www.get-digital-help.com/format-fill-color-on-a-column-chart-based-on-cell-color/
Its was very very helpful, but as other 3 people have said, the color of the cell dont match the color of the graph.
The code you gave:
Sub ColorChartColumnsbyCellColor()
With Sheets("Color chart columns").ChartObjects(1).Chart.SeriesCollection(1)
Set vAddress = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))
For i = 1 To vAddress.Cells.Count
CS = ThisWorkbook.Colors(vAddress.Cells(i).Interior.ColorIndex)
R = CS Mod 256
G = CS \ 256 Mod 256
B = CS \ 65536 Mod 256
.Points(i).Format.Fill.ForeColor.RGB = RGB(R, G, B)
Next i
End With
End Sub
didnt help to fix the problem. please may you help us?
Thank you very much and have a good day