Coloring Chart Area Dynamically?

[silver23]

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?

[Rowan Drummond]

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?

[silver23]

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?
>

[silver23]

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?
> >

[Rowan Drummond]

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?
>>>

[silver23]

Excellent! Thanks!