Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Keep Pivot Chart formatting static (Excel 2003)

    Hi,

    I know this is a sore point in Excel. <img src=/S/sad.gif border=0 alt=sad width=15 height=15> I have a large sheet of source data of which I have a Pivot Chart. Above the chart I have Page Buttons to filter out data to display. The initial problem is that I need all companies (displayed by the lines on the chart) to be certain static colours (not the default Excel colours). Each time I refresh the data, the colours go back to default colours. HansV said that this is a bug/feature according to <post#=447120>post 447120</post#>. So I guess I will need a custom macro to re-format the companies to a assigned colour.

    The second issue is (assuming the chart is using default colours), that if I have 3 companies (for argument sake) The lines could be BLUE, RED, YELLOW. Now if I filter to show company one and three only the colour of company three becomes RED (where it was yellow)!! I need the macro to keep the colours static, but also to ensure that if I filter data that the company retains its original assigned colour irrespective of the amount of companies shown.

    How can we go about helping each other to sort this problem out?
    TIGA (Tx in grateful advance)
    Regards,
    Rudi

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

    Re: Keep Pivot Chart formatting static (Excel 2003)

    > How can we go about helping each other to sort this problem out?

    Start by attaching a sample workbook.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Keep Pivot Chart formatting static (Excel 2003)

    Done....I will get it to you on my next break.....
    TX
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Keep Pivot Chart formatting static (Excel 2003)

    Hi Hans,

    Here is the WB.

    If you filter the pivot chart to the State OR, then you will see only ComA and ComC. CompanyB is not available. Now ComC's colour becomes PINK where it was YELLOW.

    Besides this inconsistency (with Excels default colours), I would like ComA, B and ComC to have a static colour throughout ... say:
    ComA must be BLUE
    ComB must be RED
    ComC must be GREEN

    Hope this is a stating point to work from...
    Regards,
    Rudi

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

    Re: Keep Pivot Chart formatting static (Excel 2003)

    Put the following code in the code module of the Chart sheet:

    Private Sub Chart_Calculate()
    Dim ser As Series
    For Each ser In Me.SeriesCollection
    Select Case ser.Name
    Case "ComA"
    ser.Border.Color = vbRed
    Case "ComB"
    ser.Border.Color = vbGreen
    Case "ComC"
    ser.Border.Color = vbBlue
    End Select
    Next ser
    End Sub

    Modify the colors to your preference. You can also use ColorIndex instead of Color, and use an index number in the range 1 ... 56.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Keep Pivot Chart formatting static (Excel 2003)

    Rudi,
    FWIW, this is not an inconsistency - Excel assigns colours in series order. Since ComC goes from being series 3 to series 2, its colour changes; however, the colour assigned to series 2 is consistent throughout!
    (Personally I hate pivot charts and avoid them if at all possible)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Keep Pivot Chart formatting static (Excel 2003)

    Hi Rory,

    I guess that was just an incorrect use of the word. I realise that Excel uses the colours in a consistent way based on the series order. I just meant that it was not consistent for my purposes...
    Sorry....and Sorry MS!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Keep Pivot Chart formatting static (Excel 2003)

    Hans,
    Tx for the code....it eventually looks easier than I anticipated. I was picturing some major code to toggle things and do weird stuff to get what I needed.
    Anyways, the code is working great in the workbook I sent. I will customise it to the genuine WB. I cannot forsee problems there!
    Cheers
    Regards,
    Rudi

Posting Permissions

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