Archive of Mr Excel Message Board

Check out our Excel Charting Resources
Back to Charting for Excel archive index
Back to archive home

Chart Conditional Formatting

Posted by Doug on December 12, 2000 9:59 AM
Does anyone have a suggestion how I can cause the color fill within a bar chart to change color depending on the value of a cell?


Re: Chart Conditional Formatting

Posted by Tim Francis-Wright on December 13, 2000 8:30 AM

I think I have what you need:

1. Set up a class module--I called mine EventClassModule

2. Put the following code in that class module:
Public WithEvents myChartClass As Chart

Private Sub myChartClass_Calculate()
Dim cht As Object
Dim p As Object
Dim V As Variant
Dim Counter As Integer

For Each cht In ActiveSheet.ChartObjects
Counter = 0
V = cht.Chart.SeriesCollection(1).Values
For Each p In cht.Chart.SeriesCollection(1).Points
Counter = Counter + 1
Select Case V(Counter)
Case Is > 0.75
p.Interior.ColorIndex = 4 ' Green
Case Is > 0.5
p.Interior.ColorIndex = 46 ' Orange
Case Is > 0.25
p.Interior.ColorIndex = 6 ' Yellow
Case Else
p.Interior.ColorIndex = 3 ' Red
End Select
End Sub

3. In the Sheet object in question, include the
following code:

Dim myClassModule As New EventClassModule

Private Sub Worksheet_Activate()
Dim cht As Object
For Each cht In ActiveSheet.ChartObjects
Set myClassModule.myChartClass = cht.Chart
End Sub

This worked for me, using a very simple column
chart with one series and values between 0% and
100%... but you should be able to modify the
Calculate code as you need.

Good luck!

This archive is from the original message board at
All contents © 1998-2004
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.