Graph fill based on criteria
Posted by Paul on January 08, 2002 3:22 AM
I have a chart that I change the bar fill based upon if the data is above 500 or below 500 (each a different fill). I'm sure there has to be a way to accomplish this in VBA. Any help would be appreciated.
Posted by Bruno on January 08, 2002 5:53 AM
Hi Paul,
I hope this is what you are looking for.
For example
cells a2:b2 containing titles
cells a3:a5 = jan, feb, mrt
cells b3:b5 = 150, 200, 600
When you want the change the chart based on the value of B5 :
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("B5") < 500 Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(3).Interior.ColorIndex = 50
Else
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(3).Interior.ColorIndex = 3
End If
Range("B5").Select
End Sub
-----
Posted by Mark W. on January 08, 2002 7:38 AM
Another way...
...to accomplish this without resorting to VBA is
to create a Stacked Column Chart. For example,
suppose cells A1:A5 contains...
{"Series1"
;400
;600
;750
;250}
Enter the formula, =MAX(0,A2-500), into cell B2
and copy down to cell B5. Select cells A1:B5 and
create a chart using the Stacked Column chart type.
Posted by Mark W. on January 08, 2002 7:50 AM
A variation of the same approach...
In the event that you want the entire column set
to a different pattern (not just the "tip" that
exceeds your limit). Then (using the same data
as before) enter the formula, =IF(A2>500,A2,0),
into cell B2 and copy down to cell B5. Create a
standard (side by side) column chart. Select
a data series, choose the Format | Selected Data
Series... menu command, and set the Options
"Overlap" value to 100.
Posted by Paul on January 08, 2002 10:40 AM
Just what I was looking for!!
Exactly what I was looking for!! Thanks Mark
