Conditional formatting backgrounds in charts

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/...html#CondChart
and
http://peltiertech.com/Excel/Charts/...tml#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.

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 wrote:
> 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/...html#CondChart
> and
> http://peltiertech.com/Excel/Charts/...tml#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, Microsoft MVP - Excel
http://www.andypope.info

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" wrote:

> 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 wrote:
> > 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/...html#CondChart
> > and
> > http://peltiertech.com/Excel/Charts/...tml#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, Microsoft MVP - Excel
> http://www.andypope.info
>

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 wrote:
> 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" wrote:
>
>
>>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 wrote:
>>
>>>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/...html#CondChart
>>>and
>>>http://peltiertech.com/Excel/Charts/...tml#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, Microsoft MVP - Excel
>>http://www.andypope.info
>>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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" wrote:

> 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 wrote:
> > 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" wrote:
> >
> >
> >>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 wrote:
> >>
> >>>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/...html#CondChart
> >>>and
> >>>http://peltiertech.com/Excel/Charts/...tml#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, Microsoft MVP - Excel
> >>http://www.andypope.info
> >>
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>

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


Kyle wrote:
> 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" wrote:
>
>
>>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 wrote:
>>
>>>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" wrote:
>>>
>>>
>>>
>>>>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 wrote:
>>>>
>>>>
>>>>>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/...html#CondChart
>>>>>and
>>>>>http://peltiertech.com/Excel/Charts/...tml#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, Microsoft MVP - Excel
>>>>http://www.andypope.info
>>>>
>>
>>--
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info