Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Color the Bars (Excel 2000)

    Hi all,

    A question about chart bars and the colors used for them:

    When creating a chart (in this case a bar chart but could apply to other types as well), the color for each bar is initially determined by excel. One can manually change the colors of the bars.

    What is desired, however, is that the color of the bar be chosen based on its height. The application is charting how students perform on a test or their overall average. So, for students above 90, say, the bar should be green; 70-90 it should be blue; below 70 - red. Want something that automates this task.

    1. Can this be done w/o VBA and w/o resorting to clicking on each student's bar and choosing the color? Have tried a few solutions:
    - conditional formatting the cell from which the chart is done to reflect above colors - nothing
    - selecting the bars of a common color while holding the CTRL key - nope (just got last bar anyway)

    2. If a VBA solution is necessary, that's ok. We thought a solution might start out w/ a lookup table that listed the test ranges for each color and it's corresponding color. The code would then go thru the chart (each data series in the chart?), get the value of the data point corresponding to the bar/series, check the lookup table for the color, change the color of the bar, loop. But any VBA solution, w/ or w/o a lookup table would be useful.

    TIA

    Fred

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color the Bars (Excel 2000)

    You can color the bars manually and record the code at the same time. I've done that and came to the following code:
    <pre>Sub Macro1()

    ActiveSheet.ChartObjects("Graph 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(5).Select

    With Selection.Border
    .Weight = xlThin
    .LineStyle = xlAutomatic
    End With
    Selection.Shadow = False
    Selection.InvertIfNegative = False
    With Selection.Interior
    .ColorIndex = 40
    .Pattern = xlSolid
    End With

    End Sub
    </pre>


    I here selected the fifth point (or bar) in a series by just clicking first the bar, waiting one or two seconds, then click one time more, so that the fifth bar or point became solely selected. If you then right-click, you can choose the format bar option in the popup menu and change the color. You still have to modify the code to your needs, of course.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Color the Bars (Excel 2000)

    Hi Hans,

    Thks for the help. I'm not sure what the code is doing - while I've done some Excel VBA, little to none of it has involved charts. But from what I guess of your code, it's not clear that it's doing what I need. It's even more clear that I probably could not modify it to reach the goal.

    I know how to record a macro while I'm using the chart wizard and know how to go into the VBA window to look at and modify code. I could also record a macro to click on a desired bar and change its color, which is I guess what you've done. But why did you click on the first bar, wait, and then click the 5th bar?

    I'm not sure where your code is selecting a color based on the bar height or data series number. The statement with the .ColorIndex provides a color, I assume (whatever corresponds to 40). But what we're trying to avoid is having to run the macro for each bar. Are the last few statements (With Selection.Interior) looping thru the bars? How would I read a range in the worksheet that was a lookup table that has the colors that correspond to the different bar heights? After determining the point in the table that corresponds to the present bar, how would I take the desired color and use that in a ColorIndex statement?

    thks

    fred

Posting Permissions

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