+ Reply to Thread
Results 1 to 5 of 5

Change bar chart fill colors to match the color of a random cell

  1. #1
    Registered User
    Join Date
    12-31-2013
    Location
    Santa Clara, CA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Change bar chart fill colors to match the color of a random cell

    First time posting, not strong at all with VBA or Macros. Cannot find this answer anywhere: trying to produce a bar chart of Suppliers and their associated Spend, where the fill color of the generated bar chart matches that of the strategy (Growing = green, sustaining = yellow, bad/exit = red). I need to be able to either have the fill color change automatically (VBA) or through a whole series of hidden fields that do the calculations.
    I've posted a picture that shows exactly what I want as the output. I did the coloring within the chart manually of course, but in reality I will have 1000 "suppliers" and the "Strategy" could change at anytime - a field populated by a widespread team. I can use conditional formatting well, which is what I used to manipulate the cell color shown in the "strategy" column C. However, I have no idea how to connect that color (Column C) to the graph where I want the Supplier/Spend data displayed to match in color (Red, Green, or Yellow).

    If someone could generate the VBA formula (less preferred), or walk me through an idea how to generate a chart based on the 3 intended colors (more preferred), I would greatly appreciate the support!

    ScreenShot116.jpg

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Change bar chart fill colors to match the color of a random cell

    Configure your data something like this...

    A B C D E F
    1 Supplier Spend Strategery Grow Exit Sustain
    2 Supplier1
    $10
    Grow
    $10.00
    3 Supplier2
    $9
    Exit
    $9.00
    4 Supplier3
    $8
    Sustain
    $8.00
    5 Supplier4
    $7
    Grow
    $7.00
    6 Supplier5
    $6
    Exit
    $6.00
    7 Supplier6
    $5
    Sustain
    $5.00

    Worksheet Formulas
    Cell Formula
    D2 =IF($C2=D$1,$B2,"")
    E2 =IF($C2=E$1,$B2,"")
    F2 =IF($C2=F$1,$B2,"")

    Then make a bar chart of the three data series in columns D:F (Grow, Exit, Sustain) and color each series Red, Green, Yellow. Then when you add/change a spend value or strategy, the chart would automatically update.

    This site gives a step-by-step example.
    Conditional Formatting of Excel Charts
    Last edited by AlphaFrog; 12-31-2013 at 08:09 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Change bar chart fill colors to match the color of a random cell

    Here's an alternative..

    Select the "Strategy" value from the dropdown in Column C for each Supplier..

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by apo; 01-01-2014 at 06:45 AM.

  4. #4
    Registered User
    Join Date
    12-31-2013
    Location
    Santa Clara, CA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Change bar chart fill colors to match the color of a random cell

    APO,
    The VBA was fantastic, but I ran into problems when linking to an INPUTS tab and it also didn't seem to refresh correctly after changing the data. I've attached a better example, exactly what it would look like in our operating model. Can you help finish?

    - INPUTS tab is used by a widespread team. The graph output is to be on a separate tab, for use to copy to PowerPoint or other, but should link to the INPUTS tab.
    - The list can grow up to 700 line-items or 'Suppliers'
    - Tab names changed, had trouble linking this correctly in the VBA
    - GREEN color in graph should be darker, to match standard green used on the text already

    Would appreciate additional help to update the logic & populate the two graphs shown. Thanks again - a great help. ThenSet Charts COlumn Colors Dynamically_REV2.xlsm

  5. #5
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Change bar chart fill colors to match the color of a random cell

    Hi..

    The attached should do what near abouts what you want..

    Things done:

    1. Make chart range dynamic by using a Named Range
    =OFFSET('SPEND GRAPH'!$A$1,1,0,COUNTA('SPEND GRAPH'!$A:$A)-1,2)

    2. Make "Settings" range dynamic
    =OFFSET('SPEND GRAPH'!$A$1,1,0,COUNTA('SPEND GRAPH'!$A:$A)-1,6)

    3. Change it so the Charts get updated via a button instead of the Worksheet change event.. this is so you don't have to wait for the Charts to update everytime you change/add a value.. it may only be a few seconds each time.. but that will get annoying if you want to say update 50 values quickly..

    4. Changed code to suit..

    btw.. you should acknowledge the help given by AlphaFrog (regardless of which solution you use)..
    Attached Files Attached Files
    Last edited by apo; 01-03-2014 at 08:29 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Can I change the color of labels in a 3D pie chart to match cell colors?
    By Macphersonian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2013, 12:07 PM
  2. Macro to change cell border colors from one color to another
    By Hanskubansku in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2012, 07:41 AM
  3. Change Cell Fill Color Based on Colors in 2 Columns
    By djkante in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2010, 08:07 PM
  4. [SOLVED] Cell fill colors in Excel change when copying to a new book
    By jkbond67 in forum Excel General
    Replies: 2
    Last Post: 07-31-2006, 04:53 PM
  5. Can I change the colors in the fill color chart?
    By Genesis Pat in forum Excel General
    Replies: 2
    Last Post: 03-28-2006, 02:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1