[ms office - 2010] Excel Chart Problem

[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?

[Steve21]

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)

 

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:

 

 

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

 

Steve

[TechSupp]

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