+ Reply to Thread
Results 1 to 7 of 7

Consistent Colors for Pivot Charts

  1. #1
    Registered User
    Join Date
    06-05-2006
    Posts
    13

    Consistent Colors for Pivot Charts

    Hello,

    I am creating a pivot chart and I have a drop down boxes on the top and on the right (I'm sorry, I don't know the terminology). Anyway, let's say that I have 3 items showing in the drop down box on the right. If I make each item a customer color (by double clicking the the little color box to the left of each item listed under the drop down), the colors are reflected in the chart. However, as soon as I try to display new data, excel automatically diffaults to the basic colors and not the colors that I chose. Thus, the chart that originally looked great now looks different and I have to go in and again double click the little color boxes to stay consistent. How can I keep these colors constant? In other words, how can I associate a set color with items in the right most drop down box of a pivot table?

    thank you so much, I just can't figure this one out!
    Last edited by zasskar; 12-07-2006 at 07:24 PM.

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    Hi,

    Have you thought of an event macro ? ...

    HTH
    Carim

  3. #3
    Registered User
    Join Date
    06-05-2006
    Posts
    13

    changing legend key colors

    Hi,

    I guess the drop down on the right is called the legend key.

    I didn't try an event macro, and the reason is that when I record a regular macro, the macro works fine if the legend key doesn't change. However, there are some situations where I have two entrys and other situations when I have 4 entries. Thus, if I run the macro I get the error:

    Run-time error '1004':
    Unable to get the LegendsEntries property of the Legend class.

    ---
    I've attached an example of what I'm trying to do. Basically, let's say that I have 4 cars on the lot and some are 2 door and some are 4 door. I want the legend to reflect the color of the car. A white car will have a white chart bar and a blue car will have a blue chart bar. I can set it once manually, but as soon as I select a different car from the pivot table (on the top drop-down)the colors default to the excel colors.

    Thank you for any help!
    michael
    Attached Files Attached Files

  4. #4
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    Hi,

    With a little VBA coding, you can have for each of your series,
    an instruction to set the Interior.ColorIndex to whatever color you wish to have ...

    HTH
    Carim

  5. #5
    Registered User
    Join Date
    06-05-2006
    Posts
    13

    Vba

    Hi!

    That idea seems like it would work. I've only done minimal VBA so I'll check it out. Thank you for providing the "Interior.ColorIndex" hint to get it started.

    If somebody has already done this, can you please post an example?

    Thanks again,
    michael

  6. #6
    Registered User
    Join Date
    06-05-2006
    Posts
    13

    solution

    Hello,

    here is the solution I came up with for this case.

    Private Sub Chart_Calculate()
    Dim i As Integer
    Dim seriesNameValue As String ' holds name of the series within the legend (ex: white, blue, etc)


    'there has to be a better way then this "if". Is it possible to get the name of the series field,
    ' which would be "car color" here?
    'the pivot chart may be manipulated many ways. If we have more than 4, have different field button besides color
    If ActiveChart.Legend.LegendEntries.Count <= 4 Then


    For i = 1 To ActiveChart.Legend.LegendEntries.Count

    seriesNameValue = ActiveChart.SeriesCollection(i).Name

    Select Case seriesNameValue

    Case "Blue"
    ActiveChart.SeriesCollection(i).Interior.ColorIndex = 5
    Case "Red"
    ActiveChart.SeriesCollection(i).Interior.ColorIndex = 3
    Case "Yellow"
    ActiveChart.SeriesCollection(i).Interior.ColorIndex = 6
    Case "White"
    ActiveChart.SeriesCollection(i).Interior.ColorIndex = 2

    Case Else
    'do nothing

    End Select
    Next i
    End If

    End Sub

  7. #7
    Registered User
    Join Date
    01-15-2008
    Posts
    3

    Where does this code go?

    I have no experience with this VBA. Where/how do I put this into my sheet?

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1