# Excel Chart Problem

• 23rd December 2012, 05:33 PM
TechSupp
Excel Chart Problem
Our school shows the attendance of each class as a bar chart and colours the top three attending classes with Green for top, yellow fro next and red of third best. They have to format the colour of the relevant colums each time the table is updated each week and they asked if they could do this automatically (formatting of the top three classes), My answer was possibly, I dont know but I know of a group of people who might....thats where Edugeek steps in to save the day! So does anyone know if this can be done?
• 23rd December 2012, 08:04 PM
Steve21
Quote:

Originally Posted by TechSupp
Our school shows the attendance of each class as a bar chart and colours the top three attending classes with Green for top, yellow fro next and red of third best. They have to format the colour of the relevant colums each time the table is updated each week and they asked if they could do this automatically (formatting of the top three classes), My answer was possibly, I dont know but I know of a group of people who might....thats where Edugeek steps in to save the day! So does anyone know if this can be done?

Not quite automatic, but easy enough with a button (or assign the macro to when you click on graph I guess)

Code:

```Sub Chart1_Click()     Dim classes As Series     Dim i As Long     Set MyRange = Range("c5", "c14")             Sheets("Sheet1").Select     ActiveSheet.ChartObjects("Chart 2").Activate     ActiveChart.PlotArea.Select     With ActiveChart         For Each classes In .SeriesCollection             For i = 1 To classes.Points.Count                 classes.Points(i).Interior.Color = RGB(0, 0, 255)             Next i         Next classes     End With     With ActiveChart         For Each classes In .SeriesCollection             For i = 1 To classes.Points.Count                 If classes.Values(i) = Application.WorksheetFunction.Large(MyRange, 1) Then classes.Points(i).Interior.Color = RGB(0, 255, 0)                 If classes.Values(i) = Application.WorksheetFunction.Large(MyRange, 2) Then classes.Points(i).Interior.Color = RGB(255, 255, 0)                 If classes.Values(i) = Application.WorksheetFunction.Large(MyRange, 3) Then classes.Points(i).Interior.Color = RGB(255, 0, 0)             Next i         Next classes     End With End Sub```
Obviously need to change things like graph name, and range of data.

But this does as below:

Attachment 16534

Any help? (You probably could make it automatically do it as soon as series is changed, but I'm being lazy atm :) )

Steve
• 23rd December 2012, 08:41 PM
TechSupp
Thanks, I knew I could rely on Edugeek to save the day! I'll assign it to a button. Many thanks.