Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consistent colors for Bar charts (Excel 2003)

    I am using Pivot charts where I'll have up to 10 column fields with colored bar charts. So, for each value along the x-axis, I'll have as many vertical bars as I've selected among the columns. Sometimes I may wish to only look at 2 or 3 of the 10 available. Excel seems to assign colors (for the bars) in a default order. So, if for example I display all 10 initially, the 7th will be in whatever is Excel's 7th color. But if I then want to display only numbers 3, 6, and 7, the 7th will now be in Excel 3rd color rather than its 7th. I'd like to have each of the 10 keep the same colored bar no matter whether I display some or all of them. Is there a way to do this?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Consistent colors for Bar charts (Excel 2003)

    This is basically the same idea as <post:=641,780>post 641,780</post:>. You'll have to write a macro that applies the color you want to each series based on its name instead of its position.

  3. #3
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent colors for Bar charts (Excel 2003)

    Thanks, I'll check that out.

  4. #4
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent colors for Bar charts (Excel 2003)

    Sorry, but while assigning format by series name rather than position certainly sounds like the solution, I don't actually know how to do it. I've tried, but have not found out how this is done.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Consistent colors for Bar charts (Excel 2003)

    Could you post your workbook with the pivot chart, or a smaller version of it? Zip the file if necessary.

  6. #6
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent colors for Bar charts (Excel 2003)

    Here is a stripped down version that shows that I am talking about. Thanks, Hans!
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Consistent colors for Bar charts (Excel 2003)

    You can use a macro like this:

    Sub UpdateChart()
    Dim cht As Chart
    Dim ser As Series
    Set cht = Charts("Chart1")
    cht.PivotLayout.PivotTable.RefreshTable
    For Each ser In cht.SeriesCollection
    ' Modify, add and delete as needed
    Select Case ser.Name
    Case "AA"
    ser.Interior.ColorIndex = 3
    Case "CM"
    ser.Interior.ColorIndex = 4
    Case "HV"
    ser.Interior.ColorIndex = 5
    Case "NG"
    ser.Interior.ColorIndex = 6
    Case "PN"
    ser.Interior.ColorIndex = 7
    Case "SO"
    ser.Interior.ColorIndex = 8
    Case "TQ"
    ser.Interior.ColorIndex = 9
    Case "TS"
    ser.Interior.ColorIndex = 10
    End Select
    Next ser
    End Sub

    The attached version has a command button (from the Forms toolbar) that executes this macro.
    Attached Files Attached Files

  8. #8
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent colors for Bar charts (Excel 2003)

    Thanks as always, Hans! This will really help me.

Posting Permissions

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