Office Software Thread, Excel Chart Problem in Technical; Our school shows the attendance of each class as a bar chart and colours the top three attending classes with ...
1. ## 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?

2. 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:

Capture.JPG

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

Steve

3. ## Thanks to Steve21 from:

TechSupp (23rd December 2012)

4. Thanks, I knew I could rely on Edugeek to save the day! I'll assign it to a button. Many thanks.

SHARE: