Automatically Set Chart Series Colors to Match Source Cell Colors

August 10th, 2011 by datapig Leave a reply »

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…

  1. Sub CellColorsToChart()
  2. Dim oChart As ChartObject
  3. Dim MySeries As Series
  4. Dim FormulaSplit As Variant
  5. Dim SourceRange As Range
  6. Dim SourceRangeColor As Long
  7.  
  8. 'Loop through all charts in the active sheet
  9. For Each oChart In ActiveSheet.ChartObjects
  10.  
  11.     'Loop through all series in the target chart
  12.    For Each MySeries In oChart.Chart.SeriesCollection
  13.        
  14.         'Get Source Data Range for the target series
  15.        FormulaSplit = Split(MySeries.Formula, ",")
  16.        
  17.         'Capture the first cell in the source range then trap the color
  18.        Set SourceRange = Range(FormulaSplit(2)).Item(1)
  19.         SourceRangeColor = SourceRange.Interior.Color
  20.  
  21.         On Error Resume Next
  22.         'Coloring for Excel 2003
  23.        MySeries.Interior.Color = SourceRangeColor
  24.         MySeries.Border.Color = SourceRangeColor
  25.         MySeries.MarkerBackgroundColorIndex = SourceRangeColor
  26.         MySeries.MarkerForegroundColorIndex = SourceRangeColor
  27.        
  28.         'Coloring for Excel 2007 and 2010
  29.        MySeries.MarkerBackgroundColor = SourceRangeColor
  30.         MySeries.MarkerForegroundColor = SourceRangeColor
  31.         MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
  32.         MySeries.Format.Line.BackColor.RGB = SourceRangeColor
  33.         MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
  34.    
  35.     Next MySeries
  36. Next oChart
  37.  
  38. 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.

You can download a demo here

.

Hopefully, this is useful to somebody out there.

Advertisement

42 comments

  1. David Onder says:

    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

  2. David Smith says:

    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/?

  3. Lynda says:

    Nifty! I’ve got a couple of things this will come in handy for.

  4. Ed says:

    Cool– I’ll use it tomorrow

  5. AlexJ says:

    Who are you calling a “dashboarding nerd”?

    Regards,
    A Dashboarding Nerd

  6. Chris says:

    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

  7. datapig says:

    Chris: Great work! I’ll highlight your code in a blog post this week.

  8. Chris says:

    Thanks dp.
    Can you put this link where I uploaded a demo with the code?

    http://chrisumphlett.com/?p=168

  9. Joe Lavery says:

    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

  10. Harry Flashman says:

    Excellent. Exactly what I have been looking for. Cheers.

  11. Matt says:

    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.

  12. Nabil Jaffar says:

    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.

  13. Leah says:

    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

  14. shyama says:

    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

  15. lanser says:

    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

  16. Adam L says:

    Handy!…worked like a charm. Thanks!

  17. David C says:

    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?

  18. Mia Kelly says:

    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.

  19. Jann says:

    Dosn’t work with Conditional Formatting colours :(

  20. JohnZ says:

    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.

  21. Theo Schmid says:

    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! :-)

  22. JP says:

    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 :-/

  23. 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.

  24. pankaj says:

    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.

  25. SB says:

    This is great, but does anyone have the adjusted code for using the conditional formatted colour of the source cells?

  26. Satz says:

    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

  27. Emily says:

    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?

  28. Ken says:

    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

  29. Rich M says:

    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!

  30. robert says:

    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.

  31. george says:

    Hey this is just what I needed. Awesome.

  32. colin says:

    Hi,

    Has anyone worked out how to get this to work with conditional formats? I don’t have a clue about writing macros and have a small idea about hacking and slashing them together from what i find with google….

  33. Marcus Park says:

    Very helpfull code. I would like to use it but I need help to add some lines
    Week 1 600 500 450 420 340 etc
    Week 2 610 510 450 440 36 etc
    Week 3 620 520 450 460 380 etc
    etc.

    The three first cells containing the values (600, 500, 450) have the same color; red. The two last cells containing the values (420, 340)´have another color; blue. Your code Capture the first cell in the source range then trap the color Set SourceRange = Range(FormulaSplit(2)).Item(1)
    I would like to capture the color of each cell and change the only the color of corresponding part of the graph which means that each graph has red color for tre first values and blue for the last two and that will be repeated for hole the serie.
    Wolud you please help me? Thanks in advance!

  34. Benz Daniel says:

    Hi

    Worked realy great, it’s possible to change the Fontcolor to?

    Thanks for your help

  35. Jo Pedder says:

    Hello, I have never used macros before – I’ve found VB and how to insert – module. I have then copied and pasted the code and tried running it, but each time I get “compile error Syntax error”.

    I’m sure there will be something very simple to fix, but would appreciate a pointer, thanks!

  36. cloe says:

    DP, thank you very much for this, it’s made my life so much easier!

    Just as an aside, I set the ‘Item’ number in

    ‘Capture the first cell in the source range then trap the color
    Set SourceRange = Range(FormulaSplit(2)).Item(1)
    SourceRangeColor = SourceRange.Interior.Color

    to zero, so: Set SourceRange = Range(FormulaSplit(2)).Item(0), which seems to mean that I only need to colour in the column headers, not the values themselves.

    Not sure whether that’s the ‘proper’ way to do it though….

    All the best and thanks again!

  37. Richard says:

    Hey,

    Thanks for the code. It has been a great help. I have one question. How can I use this same code but exclude borders from my columns and markers from line charts?

    Thanks in advance

Leave a Reply