When trying the suggested chart area dynamic colorization with a function
invoked from a cell withing the chart's sheet, the code seems to work, yet
the color reverts to its original. Even manually modifying the chartarea
color to 40 in debug is ignored and reverts to the original 15. Any clues
would be appreciated.

Function Test()
Dim chtRate As Excel.Chart
Dim lngColor As Long

'Make an object reference to the chart within the ChartObject
Set chtRate = ActiveSheet.ChartObjects(1).Chart

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex (works lngColor = 15)

lngColor = 40 (works lngColor = 40)

'Change the chartarea color
chtRate.ChartArea.Interior.ColorIndex = lngColor (doesn't work lngColor
remains 15)
'chtRate.ChartArea.Interior.ColorIndex = 40

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex (after execution, both=15)


'Return the chartarea color back to original color.
'chtRate.ChartArea.Interior.ColorIndex = lngColor

'Set chtRate = Nothing
End Function

"Jim Cone" wrote:

Vickie,

I believe both should work.
However, you will be much happier by not trying to select the chart,
but by setting an object reference to the chart and using the object
reference to do things.

Also, the syntax for a chart on a Worksheet and a separate Chart Sheet are different.
A chart on a worksheet in contained within a ChartObject. A chart sheet is a chart.
If that doesn't make sense, then I believe that was Microsoft's intent.<g

Take a look at the following example, that changes the background color
of a chart on a worksheet. It should work on the first chart shown on
the active worksheet (including your "rate" chart).
(Be aware that there is a separate newsgroup devoted only to charts...
"microsoft.public.excel.charting")
'----------------------------------
Sub Test()
Dim chtRate As Excel.Chart
Dim lngColor As Long

'Make an object reference to the chart within the ChartObject
Set chtRate = ActiveSheet.ChartObjects(1).Chart

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex

'Change the chartarea color
chtRate.ChartArea.Interior.ColorIndex = 40

MsgBox "Chart color changed "

'Return the chartarea color back to original color.
chtRate.ChartArea.Interior.ColorIndex = lngColor

Set chtRate = Nothing
End Sub
'----------------------------------

Regards,
Jim Cone
San Francisco, USA


"vickie_raven" wrote in message
...
I have a chart on a work sheet, and the sheet is called "Operator
Input" and the charts name is "rate"
My question is - How do i select the chart?
i have tried this
ActiveSheet.ChartObjects("rate").Select
and i get an error
but this seems to work
ActiveSheet.ChartObjects("rate").Activate
is the Activate the only way to do the selection??
Thanks in advance
Vickie