MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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