+ Post New Thread
Results 1 to 3 of 3
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. #1
    TechSupp's Avatar
    Join Date
    Mar 2007
    Location
    South Yorkshire
    Posts
    1,887
    Thank Post
    299
    Thanked 120 Times in 101 Posts
    Rep Power
    41

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

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,696
    Thank Post
    335
    Thanked 515 Times in 483 Posts
    Rep Power
    179
    Quote Originally Posted by TechSupp View Post
    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. #3
    TechSupp's Avatar
    Join Date
    Mar 2007
    Location
    South Yorkshire
    Posts
    1,887
    Thank Post
    299
    Thanked 120 Times in 101 Posts
    Rep Power
    41
    Thanks, I knew I could rely on Edugeek to save the day! I'll assign it to a button. Many thanks.

SHARE:
+ Post New Thread

Similar Threads

  1. Excel sum problem
    By wesleyw in forum How do you do....it?
    Replies: 8
    Last Post: 29th August 2009, 11:23 AM
  2. [MS Office - 2007] Excel 2007 problem
    By nc- in forum Office Software
    Replies: 4
    Last Post: 18th March 2009, 04:14 PM
  3. Excel 2003 problem with chart...
    By kennysarmy in forum Windows
    Replies: 6
    Last Post: 12th December 2008, 07:04 PM
  4. Excel VBA problems
    By cursong in forum Coding
    Replies: 1
    Last Post: 13th October 2008, 11:28 AM
  5. excel lookup problem, can someone help me please?
    By RabbieBurns in forum Windows
    Replies: 3
    Last Post: 2nd July 2008, 12:51 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •