Coloring Chart Area Dynamically?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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.
 
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
 
Rowan,

Hi! I know you responded to this long ago, but this is really helping me. I
used your code, but it only changes the color when I execure the code via the
VB Tool. When I save the macro into the sheet and re-open it, it does not
change the color accordingly.

please help,
justin
 
Back
Top