While working on a dashboarding project here, I accidently developed a charting macro that will automatically set the series colors in a chart to match their source cell's colors.
.
I'm not sure how useful this actually could be, but it's charming enough to share with my 12 fans.
.
Let's say I created this chart – and I want to apply specific coloring to each series without having to painstakingly change all the color options in each series.
.
All I have to do is color the source cells that feed my chart series….
.
…then I run this macro…
-
Sub CellColorsToChart()
-
Dim oChart As ChartObject
-
Dim MySeries As Series
-
Dim FormulaSplit As Variant
-
Dim SourceRange As Range
-
Dim SourceRangeColor As Long
-
-
'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
-
-
'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
-
-
On Error Resume Next
-
'Coloring for Excel 2003
-
MySeries.Interior.Color = SourceRangeColor
-
MySeries.Border.Color = SourceRangeColor
-
MySeries.MarkerBackgroundColorIndex = SourceRangeColor
-
MySeries.MarkerForegroundColorIndex = SourceRangeColor
-
-
'Coloring for Excel 2007 and 2010
-
MySeries.MarkerBackgroundColor = SourceRangeColor
-
MySeries.MarkerForegroundColor = SourceRangeColor
-
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
-
MySeries.Format.Line.BackColor.RGB = SourceRangeColor
-
MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
-
-
Next MySeries
-
Next oChart
-
-
End Sub
.
…and Zoomo! The chart will automatically adopt the colors in the source range.
.
It works for column charts too.
.
Notes:
1. To use the macro, simply copy the code in this post and paste it into a standard module.
2. As the macro is written, the chart can only adopt the color of the first cell in the series range. So you technically only have to color the first cell in source data for each series.
3. Remember this macro colors the entire chart series, not individual data points - so you couldn't use this on a Pie Chart.
4. In my limited testing, I've confirmed that this macro works in Excel 2003, 2007, 2010.
5. And before you dashboarding nerds ping me, I know that gaudy colors like Yellow, Red, and Orange are bad - I used them for demonstration purposes only.
.
Hopefully, this is useful to somebody out there.
I have actually done something similar to this except I use the label column (1st column) to get the colors. I created a KPI report a few years ago and mapped the colors to eliminate the need for the legend.
David
This can be a nice time saver for quickly formatting a chart. What would be a solution for having the same functionality for a...gasp...pie chart/?
Nifty! I've got a couple of things this will come in handy for.
Cool-- I'll use it tomorrow
Who are you calling a "dashboarding nerd"?
Regards,
A Dashboarding Nerd
A frequent powerpoint task at my office could greatly benefit from this. It's slightly different: do you think that VBA could change the color of each observation in a column graph (each column)? And could this be done in PowerPoint?
I have solved the first part-- to change in excel. I'm not sure if ppt is possible because when charting in ppt using the datatables I'm not sure you can change the colors-- there's no point.
any ideas?
code for first part:
Sub ChartColor()
'adapted from:
'Data Pig @ http://datapigtechnologies.com/blog/index.php/ _
automatically-set-chart-series-colors-to-match-source-cell-colors/#respond
'http://www.ozgrid.com/forum/showthread.php?t=141204&highlight=vntValues
Dim cht As ChartObject
Dim i As Integer
Dim vntValues As Variant
Dim s As String
Dim myseries As Series
For Each cht In ActiveSheet.ChartObjects
For Each myseries In cht.Chart.SeriesCollection
s = Split(myseries.Formula, ",")(2)
vntValues = myseries.Values
For i = 1 To UBound(vntValues)
myseries.Points(i).Interior.Color = Range(s).Cells(i).Interior.Color
Next i
Next myseries
Next cht
End Sub
Chris: Great work! I'll highlight your code in a blog post this week.
Thanks dp.
Can you put this link where I uploaded a demo with the code?
http://chrisumphlett.com/?p=168
Here's an xl sub for barchart panel colors I post in MrExcel a few years back:
Sub ChartSeriesColor()
'select one dimensional data series
'then run this sub
'chart 1 is located w/i active sheet
Dim DataSeries As Range 'highlighted one dimensional range
Dim ThisSeries As Integer 'counter for series number
ThisSeries = 0
For Each DataSeries In Selection 'for each highlighted cell
ThisSeries = ThisSeries + 1
InteriorColor = DataSeries.Interior.ColorIndex 'get cell color
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Points(ThisSeries).Select 'for a single series point
With Selection.Interior
.ColorIndex = InteriorColor 'set chart column to cell color
.Pattern = xlSolid
End With
Windows("Book1").Activate 'back to highlighted range
Next DataSeries
End Sub
Excellent. Exactly what I have been looking for. Cheers.
Is there a way to make this work if my series is located vertically in one column? I'm using bar charts to compare a few local sites with a couple hundred other sites. I'd like to be able to just change the color of the local sites so they are easy to see.
Thanks for any help/suggestions.
Great work there, just wanted to know if we could use a conditional formatting in the cells instead of manually changing/filling the cells with solid colors in each cell or series, at the moment what i was looking for was to be able to use columns with headers, and each column having values formatted by the conditional format option, now instead of selecting the first color and then filling up the bar charts with that color, i would like to use the conditional formatted cells with different colors as a range (gradient of 2 or 3 colors) so as to be able to see the drop in values by the change in color from the formatted cells. Would appreciate if any one can provide a code for that.
Hello,
I have been working with VBA for all of a few days but am desperate for help on a project. I have built a Gantt chart (clustered bar chart w/ start series hidden) that has been formatted to display repeated duration series for each task (i.e., SOX testing performed in first, second and third quarter). I am trying to display the duration series for each task as a different color (i.e., SOX Testing shown on chart are all red, IT Testing are all Blue, etc.)
I have attempted many VBA codes posted on different sites - have attempted to customize after they didn't work - have attempted to build from scratch to no avail. Currently, the xl sub posted by Joe Lavery gives me an error message "Run-time error '438': Object doesn't support this property or method." on this line:
For Each DataSeries In Selection 'for each highlighted cell
If you think you're able to help, please let me if I can provide additional information.
Thank You,
Leah
Do any one tell me . If the column A has bank names, which has to indicate as each line/series in line graph. and their line colors should be as mentioned in the coumn B( the cells have those colors).
Column A Column B
Axis red
Canara blue
Vysya black
Hi,
I'm using xl2003 and I get a 400 error here
Set SourceRange = Range(FormulaSplit(2)).Item(1)
SourceRangeColor = SourceRange.Interior.Color
The graph sheet has 3 line graphs and a bar chart all drawing data from various sections of a different sheet
regards
John
Handy!...worked like a charm. Thanks!
I like the idea of this... I need something a little more rugged and am wondering if a simple tweak to your macro can satisfy my needs.
Basically I want to be able to vary the points within a single series based on cell colour.
I have a column of data (values above below 1 are say yellow; between 1-5 are say blue; between 6-10 are red; and greater than 10 are black). If I fill the individual cells with colour it would be great if this data could transfer to a scatter plot.
My only option currently is to make 4 series and manually select the various cells in each group.
Advice?
Hi,
It worked a treat but does anyone know how to convert this to respond to a sigle cell colour rather than series, i.e if I need to colour-define industry average figure in a column chart as opposed to various companies I need to compare to it.
I am not a programmer myself but like to use VBA to make my life easier if I can find ready-written programmes on the internet.
Any help is much appreciated.
Mia: See this
http://datapigtechnologies.com/blog/index.php/color-pie-chart-slices-to-match-their-source-cells/
Dosn't work with Conditional Formatting colours
This works great but it changes all the charts on my worksheet. The code says 'Loop through all charts on the active sheet'.
How do I change the code so that it only works on an Active Sheet that I select on the worksheet? I tried to change All Charts to Active Charts, but it didn't like the syntex.
Great stuff! I learned again: To save time, google first, before you start on a code! I have been just in the middle of this for a similar solution (but cell colors based on conditional formatting) and needed a hint for how ot access the series collection object correctly and landed here with an almost perfect solution!
This is great! How could I modify it for source data using color-coded rows instead of color coded columns? I have a large source chart that'd be tricky to re-format. I know jack squat about VB so as I look at the code, nothing screams either column or row to me :-/
hi dear,
is this possible to change the chart bar color according to the cells font color thru macros if yes please help me with the coding b coz i'm a basic excel user.
please guide me with the step i need to folllow while wrighting macro.
is this possible to change the chart bar color according to the cells font color thru macros if yes please help me with the coding b coz i'm a basic excel user.
please guide me with the step i need to folllow while wrighting macro.
This is great, but does anyone have the adjusted code for using the conditional formatted colour of the source cells?
Hi,
This is one of best code i have seen to change the grap bar coIor.
I have a minor issue with the code which am not able sort. Can you please help.
Am using excel 2007. The code works perfectly fine If the backgorund color is manully applied on the cell. If conditional formatting has been done to reflect the back ground color based on the data. It picks up white color as the background color.
I have data like this
Patch | Index
-------------------
APA | 33 ---- > conditional formatting has been done so that if data > 30 cell bg color is green.
ABA | 25 -----> If value between 25 and 30. Cell bg color is yellow.
AMD | 19 ------> IF less than 25 . its will turn red.
since this a dashboard and data is autopicked from other sheet. Its diffiocult to change the color manually. So conditional formatting has been done to represent the background.
I want the same color as the background of each cell. but its not working and picking white as the backgorund for all the cells.
Thanks in advance. Any help is appreciated.
Regards,
Satz
I'm using Excel 2007 to visualize data that have 9 different attributes, and I use font colors to identify data with similar qualities - about five different colors. I create a scattergram by selecting contiguous records within a single column, with colors varying from record to record. (Each row represents a set of measurements, and the color of a row is based on the quality of the measurement. A row represents a single event of random duration, and the quality of measurement is based on event duration.) I would like to be able to color individual symbols on the scattergram with the font color used for the source record in the spreadsheet. Suggestions? Help? Guidance?
I have Excel 2010. I consider myself an intermediate Excel user but don't know how to use macros or write VBA code. I tried the above macro. it didn't work. it changed all of my columns in all of my spreadsheets on the same workbook to the same color as the first source data cell color. Also deleted all of the numerical data, so charts were wrong color and had wrong data. I most likely didn't copy or modify the macro correctly. Is there a good source for learning how to create macros? I think this particular macro would be used extremely frequently and would be very helpful if I could get it to work
Worked great ... except that if you don't specify any color, instead of leaving the chart the way it was, it picks up the 'no fill', interprets it as white and makes all your lines white. Workaround was to put some color in each column as shown in demo; then was fine.
Ques 1: is there adjustment to VB code to effect all charts in the entire workbook?
Ques 2: any way to skip a particular series if the reference cell is 'no fill' ?
Thanks!
Overall, GREAT piece of code!
What in the code will need to be modified if I am interested in having the colors change by row versus by each series? for example if I was looking at 3 homes and they were all a different prices and ages and wanted each house to have a separate color for its given properties.