Conditional formatting backgrounds in charts

[Kyle]

howdy,

I'm trying to setup a chart in excel that conditionally formats the
background colour.
I'm already using the speedometer chart
(http://peltiertech.com/Excel/Charts/Speedometer.html)
and need to conditionally format the background to be different colours such
as red, yellow or green.

To make it even more fun, I'm trying to format it with a fill effect to make
it look a lot better.

Currently I've just generated 3 versions of the same chart and just cut and
pasted the one I need each week, but this has become quite time consuming and
I'd like to find a way to automatically just set the background colour
depending on a cell value in the sheet.

Is there a way to do this? I've read many of the conditional formatting
websites linked to from here such as
http://peltiertech.com/Excel/Charts/format.html#CondChart
and
http://peltiertech.com/Excel/Charts/format.html#CondChart2
but they both deal with using the secondary chart to fake the background.
As the speedometer chart already uses the secondary axies, I am unable to use
this type of trick.

your help is greatly appreciated.

 

[Andy Pope]

Hi,

Not quite sure want your definition of background is in your particular
chart. If it is the circular area inside the dial then you could add
more rings to the donut chart.

Cheers
Andy

 

[Kyle]

Hi Andy, Thanks for responding.

By background I mean, if you right click the chart, select Format Chart
Area, and under the pattern tab, change the area colour.

Another option I have is to have the text boxes (title, x-y-axis titles,
etc..) conditionally format as well/instead.

Thanks again.

 

[Andy Pope]

Ok here is an idea if the chart is a embedded chart on a worksheet you
could make the chart transparent, by setting the chartarea and plotarea
pattern to none, and have conditional formatting on the cells underneath
the chart. And whilst the chart is see through you could then use the
cells to hold the title texts and CF them also.

If that doesn't work for you then probably VBA code will be needed. This
can be placed in the worksheets change event and only update the chart
when specific cell(s) change.

Cheers
Andy

 

[Kyle]

Thanks again Andy.

I tried the changing of the cell colours. That could work. The issue is
that I have many of these charts (about 50) on different tabs, (I'm a bit
flexible in allowing mutliple charts per tab, or just 1 per tab if that's
easier), and although they are imbeded into Excel, I have to place them into
another presentation. I played with it this morning and although I can cut
and paste all the cells, doing this 50 times is quite tedious. Instead of
just clicking the chart ctrl-c, alt tab, ctrl-v, I have to select the cell
range, and then cut and paste. Is there a chart setting for it to
automatically take in the cell formatting behind it? I wouldn't think so but
that would be nice.

I guess I'm at VBA then. I started down this path yesterday. I'm not as
familiar with Excel's VBA/Macro system. Is there a way of looping through
all the worksheets in a work book? Is there a way of looping through all the
charts on work sheet? I think I've figured out how to change the backgrounds
of each chart, it's just the looping I'm having issues with.

Thanks again for your help. Neat trick on the cell shading.

 

[Andy Pope]

This will do all worksheets and the chartobjects on them.

'-------------------
Sub Test()

Dim objCht As ChartObject
Dim shtTemp As Worksheet

For Each shtTemp In ActiveWorkbook.Worksheets
For Each objCht In shtTemp.ChartObjects
' need some conditional test to determine actual color to use
objCht.Chart.ChartArea.Interior.ColorIndex = 3 ' red
Next
Next

End Sub
'-------------------

Cheers
Andy