Solved

Change the color of a ChartSeries to correspond to Grouping

Posted on 2011-03-15
2
223 Views
Last Modified: 2012-05-11
Hi All
I’m using excel 2010. What I have is a workbook with 3 sheets; Summary, RFQEvaluation and OpenPRsAge. The data is contained in RFQEvaluation, Summary has a pivot chart linked to a pivot table in OpenPRsAge. I have written some code that will group the Age in OpenPRsAge by 10 starting at 0 and ending at 50 so there will be 6 chart series. I have also coded that the color of series 1 will be green, series 2 light green, series 3 yellow, series 4 orange, series 5 red and series 6 black. If there is data for each series it works fine. The problem I’m having is that if there is no data for 0-9 then 10-19 will be series 1 and will be green. What I need is for 0-9 to be green, 10-19 light green and so on. So in other words if there is data in 0-9 and NO data in 10-19 and again in 20-29 I need to have 20-29 to be orange and not light green. I have added a workbook.  If all the 2’s are changed to 10 0-9 falls away and the colors are not allocated correctly
Thanks in advance
Elmo
 UploadToExpertsPivotColors.xlsm UploadToExpertsPivotColors.xlsm
0
Comment
Question by:cErasmus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35137291
Try changing the code to:
Sub ChartseriesColor()

Dim AgeRange As Range


Application.EnableEvents = False
Application.ScreenUpdating = False


'**To make sure OpenPRsAge is grouped right
With Sheets("OpenPRsAge")
   .PivotTables("PivotTable1").PivotCache.Refresh

   Set AgeRange = .Cells.Find("Age")
   AgeRange.Group Start:=0, End:=50, By:=10
   With .PivotTables("PivotTable1").PivotFields("Age")
      .ShowAllItems = True
      .PivotItems("<0").Visible = False
   End With
End With
'**


'**
Dim Counter
'On Error GoTo myError
Counter = 0
Do Until Counter = 6
    Counter = Counter + 1
    
    
    With Sheets("Summary").ChartObjects("Chart 2").Chart.SeriesCollection(Counter).Format.Fill
        
        If Counter = 1 Then
            'green
            .ForeColor.RGB = RGB(0, 176, 80)
        ElseIf Counter = 2 Then
            'light green
            .ForeColor.RGB = RGB(146, 208, 80)
        ElseIf Counter = 3 Then
            'yellow
            .ForeColor.RGB = RGB(255, 255, 0)
        ElseIf Counter = 4 Then
            'orange
            .ForeColor.RGB = RGB(255, 192, 0)
        ElseIf Counter = 5 Then
            'red
            .ForeColor.RGB = RGB(255, 0, 0)
        ElseIf Counter = 6 Then
            'black
            .ForeColor.ObjectThemeColor = msoThemeColorText1
        
        End If

    End With

Loop



Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


Exit Sub

myError:
If Err.Description = "Invalid Parameter" Then
    Resume Next
Else
MsgBox Err.Description
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:cErasmus
ID: 35137462
Thank you very much
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question