Conditional formatting of chart

[jdhill]

I have a simple spreadsheet to help track a budget. The spreadsheet is a running total of purchased items by department. I have a two-column chart that shows the total budget versus the budget spent (from cells A8 and A10 respectively). I also have two cells set up that calculate the percentage of the fiscal year gone (B8) and the percentage of the total budget spent (B10). What I need to do is have the budget spent column in the chart turn red if the percentage of the total budget spent is greater than the percentage of the fiscal year passed (IF(B8>B10, "turn the column red")). I've read several tutorials on conditionally formatting charts but haven't found an example that fits my situation. My condition (percentage of total budget spent vs percentage of fiscal year gone) are not part of the actual data series being plotted. I'm thinking, if this is even possible, that VBA will have to be used. If someone could please point me in the right direction I would greatly appreciate it.
Thanks in advance.

[Andy Pope]

The majority of conditional formatting in charts is done by using multiple series, with the various formattings applied, and formula to determine which series to display a given dat point with.
If you have a 2 cluster column chart then you will need to duplicate the 2 columns on the secondary axis. The use formula to display either the column from the data on the primary axis or the secondary axis.

If you need more help post example workbook with exisiting chart and data layout and describe what information should alter the formatting.

[jdhill]

Thanks for looking into this. Attached is a mockup of what I am trying to do. My chart data is pulled from cell N1 (Total Budget) and P1 (Total Spent). I also have two cells to calculate the percentage of the budget spent (R1) and the percentage of the year passed (R2). The trigger for my conditional formatting would be if the percentage of the budget spent is greater than the percentage of the year passed (in this worksheet IF(R1>R2,....). So if this condition is met I want the "Total Spent" column in my chart to turn red. I've read online about needing to add seperate data series to get conditional formatting to work, I guess I'm not sure how to make that work in this situation which led me to think that VBA would be necessary.
Thanks again for the help.

[Andy Pope]

You can not use the normal additional series approach when using 3d chart styles, which is another good reason not to use 3d chart styles.

So code will be required.

Please Login or Register  to view this content.

The attached has an example of the additional series approach.