Results 1 to 6 of 6

Bar Chart Colors based off of value

This is a discussion on Bar Chart Colors based off of value within the Excel Questions forums, part of the Question Forums category; Excel 2010 Windows 7 Is there VBA code that will change the color of the bars in a bar chart ...

  1. #1
    Board Regular
    Join Date
    Jun 2003
    Location
    Central Ark, USA
    Posts
    92

    Default Bar Chart Colors based off of value

    Excel 2010 Windows 7

    Is there VBA code that will change the color of the bars in a bar chart based off of their value? My chart is "Chart 14" and if the value for a bar, there are 14 bars, is less than 100 I would like it to be red and greater than 100 to be green.

    Thanks,
    Alan

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,761

    Default Re: Bar Chart Colors based off of value

    Hi Alan

    If the values will never change I'd change the coulours manually. With just 14 bars it would be a matter of seconds.

    If the values will change and you want the colours to follow, use a stacked bar chart. Split the values in 2 series, one for the less than 100 and another for the others.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    Board Regular
    Join Date
    Jun 2003
    Location
    Central Ark, USA
    Posts
    92

    Default Re: Bar Chart Colors based off of value

    The values change each time the spreadsheet is updated. I will try the "Stacked Bar" and see what I can figure out.
    Thanks for the info
    Thanks,
    Alan

  4. #4
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    Default Re: Bar Chart Colors based off of value

    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    New Member
    Join Date
    Nov 2012
    Posts
    6

    Default Re: Bar Chart Colors based off of value

    So I have a stacked bar chart which generates a Gannt which works fine.

    Using the Peltier method to separate out the values, in this instance I am trying to sort by RAG (Red, Amber, Green etc) status instead of a number which works just fine.

    However I cannot seem to select ALL the data in the new table, I can select a single column only in the new one, not all. If I select all the chart breaks. The desired end state is to select the RAG and that makes the bar the matching colour.
    I have no idea why I can't select more data, something I do regularly in other documents.

    To be clear on the intent: the Gannt works fine, just pump in two dates, chart does the rest. What I want to do is select the RAG and then the corresponding bar matches the colour.

    The spreadsheet is below and I would appreciate a steer where this is going wrong.

    http://www.dead-fish.com/Files/Book1.xlsx

  6. #6
    New Member
    Join Date
    Nov 2012
    Posts
    6

    Default Re: Bar Chart Colors based off of value

    Anyone?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com