MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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?


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!