how to change background color of a chart in 2007 Excel by VBA

[feipezi]

Hi,

I tried it by Record Macro but it gives me nothing. I don't know why but Record Macro works better in 2003 than 2007.

Thanks in advance.

 

[Andrzejek]

List step-by-step of what you did that did not work.

I had a problem like yours, but I've got help here thread707-1651354

Have fun.

---- Andy

 

[judgeh59]

this is object model portion I use to set the background/interior of my pivot chart to RED. Also, if I remember correctly, I read somewhere that MS turned off the macro recorder functionality for either charts or pivot charts in 2007. Apparently they didn't have time to work on it.

With Selection.Interior
.ColorIndex = 3
.PatternColorIndex = 1
.Pattern = xlSolid
End With

hope this helps

Ernest

Be Alert, America needs more lerts

 

[feipezi]

Hi,

Message for Andy:

It's simple to list what I did: click 'Record Macro'; right-click the mouse to pick 'Format Chart Area'; in Fill, select 'Gradient fill'; then pick the color I like; then Stop Recording (the macro); then check the module, there is nothing but '...ActiveChart Selected...'.

Thanks.

Msg for Ernest:

The process that I set up is not quite straightforward.

To cut a long story short, I need to calculate some market shares of products using Pivottables, taking advantage of '% Of Row' feature of PT. But if I only want to show some of the products, not all of them, '% Of Row' will give me the wrong calculation because it needs all the products presented in the PT to get the right percentage. So I have to cut&paste what I need from a complete PT to somewhere else, then create a chart based on the pasted data. So the chart will be regular chart, not a PT chart. Sometimes, I have to combine pieces of data from multiple PT because users want to see the products from different markets in the same chart.

Of course, your tip is certainly helpful for a start. I thank you for that.

Take care.

John