Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Bar Chart Colors based off of value

  1. #1
    Board Regular
    Join Date
    Jun 2003
    Location
    Central Ark, USA
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    19,569
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bar Chart Colors based off of value

    Anyone?

Some videos you may like

User Tag List

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
  •