Conditional Chart Formatting - Excel 2003 (Win XP)

[DuncanMeyers]

Hi there,
I have a problem with trying to conditionally format my column chart.
I've read other posts to try and find an answer but none of them go into the detail I need.

I have 6 values which are plotted on the graph, each value has a different set of conditional formatting values (this is where it differs to other conditional formatting questions).

For instance I have the values:
1) 1316.6
2) 26.4
3) 127.24
4) 20
5) 167.7
6) 737.45

Now there are 3 conditions to which I would like the graph to be formatted. If the value is below Tier 1 then for the column to be Light Green, if the value is above Tier 1 then for the column to be Tan and if the value is above Tier 2 then for the column to be Rose.

My problem is that the 6 values above have the following "Tier Levels"
Tier 1 Tier 2
1) 5000 50000
2) 5000 50000
3) 10 50
4) 200 500
5) 500 2000
6) 500 2000

Which would mean values 1,2, 4 and 5 Would be Light Green, value 3 would be Rose and value 6 would be Tan.

I am using Excel 2003 on windows XP. I hope I have given you as much information as needed, but if there is anything I've missed out just ask and I'll try to inform you of it.

Thank you very much for any help, it is all appreciated.

Duncan

 

[pgc01]

Hi Duncan

Two notes:

First let me tell you that you may want to scale down the first two values at least by a factor of 10, or else when you have value 1 bigger than 50000, you don't see value 3 and maybe even some of the others because the relative difference in size is too big.

Second: This solution will automatically trigger the colour changes in the columns of the chart as values change. TO FORCE THE FORMATTING FOR THE FIRST TIME you have to change of one of the 6 values. From then on, it's automatic.

Solution:
Since you have 6 values, let's assume they are in range C2:C7.

This solution uses the worksheet change event to detect changes in the values and a sub that formats the colours of the chart.

Paste in the worksheet module where the chart is embedded:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C7")) Is Nothing Then _
    Call ColourFormatChart(Range("C2:C7"))
End Sub

Now insert a generic module and paste

Option Base 1

Sub ColourFormatChart(rR As Range)
Dim vTiers, i As Integer

vTiers = Array( _
    Array(5000, 50000), _
    Array(5000, 50000), _
    Array(10, 50), _
    Array(200, 500), _
    Array(500, 2000), _
    Array(500, 2000))

With Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection(1)

For i = 1 To 6
    
    If rR.Cells(i).Value <= vTiers(i)(1) Then
        .Points(i).Interior.Color = RGB(50, 255, 50)
    ElseIf rR.Cells(i).Value <= vTiers(i)(2) Then
        .Points(i).Interior.Color = RGB(253, 155, 0)
    Else
        .Points(i).Interior.Color = RGB(248, 152, 152)
    End If
    
Next i
    
End With

End Sub

I tested it in Sheet1 with one chart, Chart 1. You may need to change these names in the code.

You can also define a range in a worksheet where you define the Tiers values, it will be more flexible than to have it hardcoded as it is now.

You could also include the format macro in the worksheet event macro. I didn't do it because I don't like that. I like a clean and simple Worksheet module and execute code outside.

Anyway I hope this gets you started. There's room for lots of improvement.

Hope it's clear
PGC

 

[DuncanMeyers]

Hi PGC,
Thanks very much for the reply. I attempted to recreate the conditions you made the code for and it worked fine. However when I tried to move it into my own workbook it did not, I'm hoping it will just be referencing errors, but I have tried all sorts of combinations and still haven't got it working.

Unfortunately my workbook isn't that simple, the values it gets (where you said C2:C7) is infact the range:
=Stats!$C$9,Stats!$F$9,Stats!$I$9,Stats!$C$15,Stats!$F$15,Stats!$I$15

When I change a value say (Stats!$C$9 [which is 1316.6]) I get the error:

Run-time error '9':
Sub-script out of range.

And when I click debug it highlights the line in blue:

Sub ColourFormatChart(rR As Range)
Dim vTiers, i As Integer

vTiers = Array( _
    Array(5000, 50000), _
    Array(5000, 50000), _
    Array(10, 50), _
    Array(200, 500), _
    Array(500, 2000), _
    Array(500, 2000))

With Worksheets("Graph").ChartObjects("Chart4").Chart.SeriesCollection(1)

For i = 1 To 6
    
    If rR.Cells(i).Value <= vTiers(i)(1) Then
        .Points(i).Interior.Color = RGB(50, 255, 50)
    ElseIf rR.Cells(i).Value <= vTiers(i)(2) Then
        .Points(i).Interior.Color = RGB(253, 155, 0)
    Else
        .Points(i).Interior.Color = RGB(248, 152, 152)
    End If
    
Next i
    
End With

End Sub

I have uploaded my spreadsheet using yousendit so you can view the whole picture.
http://www.yousendit.com/transfer.php?action=download&ufid=7F60C6BC0FCB6A92

One other thing, say for instance my chart is not created as an object in an existing sheet but inserted into a new worksheet how do I find out the name of that chart?

And also how could I get the graph to update when the worksheet BPCS was updated? I tried this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call ColourFormatChart(Range("Sheet3!B2:B7"))
End Sub

But to no avail

One last question, to change the array values from hardcoded ones to cell references is it as simple as doing:

vTiers = Array( _ 
    Array(=Stats!$B$19, =Stats!$C$19), _ 
    Array(=Stats!$B$20, =Stats!$C$20), _ 
    Array(=Stats!$B$21, =Stats!$C$21), _ 
    Array(=Stats!$B$22, =Stats!$C$22), _ 
    Array(=Stats!$B$23, =Stats!$C$23), _ 
    Array(=Stats!$B$24, =Stats!$C$24))

?

Again any help is greatly appreciated.

Duncan

 

[pgc01]

Hi again

A few remarks

1 - Worksheets is a collection of just worksheets, no chart sheets. To refer to a chart sheet use the collections Charts or Sheets (that has both worksheets and chart sheets).

2 - Chartobjects is only for charts embedded in worksheets. This is not your case.

3 - You use Cells in a contiguous range, In this case the chart values are 6 non-contiguous cells. Therefore excel will need a range with six areas, each one with just one cell.

4 - If I understood correctely, the values change in the BPCS sheet. Therefore I used the BPCS worksheet event to trigger the graph formatting.

5 - Since the graph does not use BPCS's values I defined the range with the graphs values directely in the format sub.

6 - Since you have the tier values in the Stats sheet, you don't need the vTiers array. I used the range directly. Since it is a contiguous range (Worksheets("Stats").Range("B19:C25")) we can use the .Cells to access each individual value.


Finally, wrapping up please do the following:

In the BPCS worksheet module paste

Private Sub Worksheet_Change(ByVal Target As Range)

Call ColourFormatChart

End Sub

In the module1 (you may want to change this name as it gives you no information), paste

Option Base 1

Sub ColourFormatChart()
Dim rGraphValues As Range, rTiers As Range, i As Integer

With Worksheets("Stats")
    Set rGraphValues = Application.Union(.Range("$C$9"), .Range("$F$9"), _
        .Range("$I$9"), .Range("$C$15"), .Range("$F$15"), .Range("$I$15"))
    Set rTiers = .Range("B19:C25")
End With

With Charts("Graph").SeriesCollection(1)

For i = 1 To 6
    
    If rGraphValues.Areas(i).Value <= rTiers.Cells(i, 1) Then
        .Points(i).Interior.Color = RGB(50, 255, 50)
    ElseIf rGraphValues.Areas(i).Value <= rTiers.Cells(i, 2) Then
        .Points(i).Interior.Color = RGB(253, 155, 0)
    Else
        .Points(i).Interior.Color = RGB(248, 152, 152)
    End If
    
Next i
    
End With

End Sub

I tested this changing values in BPCS. After calculating, the graph is updated, including colours.

Hope I've understood well.
PGC

 

[DuncanMeyers]

PGC,
Wow - What more could I ask for? You have understood me perfectly and everything is working like a charm, and you've also helped me to understand whats going on as well. Which is something I find very useful and I'm sure other members do too.

I just wish there was a way of showing you my appreciation for this help.

People like you make communities like this, and infact the internet - worthwhile.

Many thanks for your time and effort,
Duncan Meyers

 

[pgc01]

I'm glad I was able to help.
I also learned a lot from this example. Although I am familiar with the concepts involved this is a very rich problem and helped me to understand better the relationships among all the elements involved.

Cheers
PGC

 

[Jon Peltier]

An easier and more transparent approach to conditional charting is to set up multiple series in the chart, one for each condition you want to highlight. I have instructions and examples here on my web site:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html