Coloring Chart Area Dynamically?

How do you ensure the charts are found when executing the macro?

"silver23" wrote:

> Thank you. When I debug the macro and step into the first statement "For
> Each chrt..." however, Expression = chrt, Value = Nothing, Type = Chart.
> Then the macro exits.
>
> "Rowan Drummond" wrote:
>
> > Maybe with a sheet calculate event on the numbers sheet:
> >
> > Private Sub Worksheet_Calculate()
> > Dim chrt As Chart
> > For Each chrt In ActiveWorkbook.Charts
> > If Me.Range("A82").Value = 1 Then
> > chrt.ChartArea.Interior.ColorIndex = 36
> > Else
> > chrt.ChartArea.Interior.ColorIndex = xlAutomatic
> > End If
> > Next chrt
> > End Sub
> >
> > This is worksheet event code. Right click the numbers sheet tab, select
> > View Code and paste the code in there.
> >
> > Hope this helps
> > Rowan
> >
> > silver23 wrote:
> > > Cell A82 can be one of two values; either zero(0) or one(1). And changes
> > > from one day to the next because it's derived from several other cells that
> > > change daily.
> > >
> > > I have four line charts on four sheets based on data in the one numbers sheet.
> > >
> > > These were manually colored with white chart areas. And white plot areas.
> > >
> > > Is there a macro available that would color the four chart areas only yellow
> > > if cell A82 is one and color white chart areas if cell A82 is zero?
> >

I ran this in a workbook with 4 charts each on it's own chartsheet and
it worked fine. If the charts were embeded as objects on normal
worksheets then the code would have to be more like this:

Private Sub Worksheet_Calculate()
Dim chrt As ChartObject
Dim i As Integer
For i = 1 To Sheets.Count
With Sheets(i)
For Each chrt In .ChartObjects
If IsNumeric(Me.Range("A82").Value) Then
If Me.Range("A82").Value = 1 Then
chrt.Chart.ChartArea.Interior.ColorIndex = 36
Else
chrt.Chart.ChartArea.Interior.ColorIndex _
= xlAutomatic
End If
End If
Next chrt
End With
Next i
End Sub

Hope this helps
Rowan

silver23 wrote:
> How do you ensure the charts are found when executing the macro?
>
> "silver23" wrote:
>
>
>>Thank you. When I debug the macro and step into the first statement "For
>>Each chrt..." however, Expression = chrt, Value = Nothing, Type = Chart.
>>Then the macro exits.
>>
>>"Rowan Drummond" wrote:
>>
>>
>>>Maybe with a sheet calculate event on the numbers sheet:
>>>
>>>Private Sub Worksheet_Calculate()
>>> Dim chrt As Chart
>>> For Each chrt In ActiveWorkbook.Charts
>>> If Me.Range("A82").Value = 1 Then
>>> chrt.ChartArea.Interior.ColorIndex = 36
>>> Else
>>> chrt.ChartArea.Interior.ColorIndex = xlAutomatic
>>> End If
>>> Next chrt
>>>End Sub
>>>
>>>This is worksheet event code. Right click the numbers sheet tab, select
>>>View Code and paste the code in there.
>>>
>>>Hope this helps
>>>Rowan
>>>
>>>silver23 wrote:
>>>
>>>>Cell A82 can be one of two values; either zero(0) or one(1). And changes
>>>>from one day to the next because it's derived from several other cells that
>>>>change daily.
>>>>
>>>>I have four line charts on four sheets based on data in the one numbers sheet.
>>>>
>>>>These were manually colored with white chart areas. And white plot areas.
>>>>
>>>>Is there a macro available that would color the four chart areas only yellow
>>>>if cell A82 is one and color white chart areas if cell A82 is zero?
>>>

Excellent! Thanks!

You're welcome.

silver23 wrote:
> Excellent! Thanks!