Assigning specific colours to PivotChart series using VBA

[kidwispa]

Hi all,

I have recorded the below macro to specify colours within a pivot chart that I plan to run when a command button is pressed. My pivot chart can contain up to three different series (Pending,Paid,Rejected) but sometimes there is only two (Paid,Rejected). Can someone please help me amend the below so that each of the series have their own specific colour, and if they are not present then they are ignored? Ideally I want the following:

Paid - Green
Pending - Orange
Rejected - Red

The macro I recorded:

Sub CWChartFormat()
'
' CWChartFormat Macro
'
'
    ActiveChart.SeriesCollection(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 128, 0)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.SeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 204, 0)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.SeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).ApplyDataLabels
    ActiveChart.SeriesCollection(3).Points(0).Select
    ActiveChart.SeriesCollection(3).DataLabels.Select
    Selection.NumberFormat = "\£0;;;"
    ActiveChart.SeriesCollection(1).Select
End Sub

Any help would be greatly appreciated

Craig

 

[Andrew Poulsom]

You can use a Select Case construct like this:

Sub Test()
    Dim i As Long
    With ActiveChart
        For i = 1 To .SeriesCollection.Count
            With .SeriesCollection(i).Fill
                Select Case .Parent.Name
                    Case "Paid"
'                       Code for Green
                    Case "Pending"
'                       Code for Orange
                    Case "Rejected"
'                       Code for Red
               End Select
            End With
        Next i
    End With
End Sub

 

[kidwispa]

Thanks for the prompt reply Andrew - just one question with your code (as I'm still learning!) the bit where you have written "Code for Red" I'm not entirely sure what I'm supposed to put in there...

Thanks again

 

[Jeffrey Smith]

This may do it.

Sub CWChartFormat()
'
' CWChartFormat Macro
'
  Dim SCount As Integer
  
  SCount = ActiveChart.SeriesCollection.Count
  With ActiveChart.SeriesCollection(1).Format.Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(0, 128, 0)
      .Transparency = 0
      .Solid
  End With
  If SCount > 1 Then
    With ActiveChart.SeriesCollection(2).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 204, 0)
        .Transparency = 0
        .Solid
    End With
  End If
  If SCount > 2 Then
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
        .ApplyDataLabels
        .Points(0).NumberFormat = "\£0;;;"
    End With
    ActiveChart.SeriesCollection(3).DataLabels.NumberFormat = "\£0;;;"
  End If
    
End Sub

 

[Jeffrey Smith]

Nice code Andrew

 

[kidwispa]

Thanks for the suggestion Jeffrey, however I'm now getting the following error:

Run time error '91':

Object variable or With block variable not set

Is it because I need to first select the sheet that the chart is on? If so, how do I do that (if not, what is it? )

 

[Jeffrey Smith]

Yes, you have to select the chart before running the macro. If you know what the chart name is ahead of time, then use the code to select the chart first.

 

[Jeffrey Smith]

Example

ActiveSheet.ChartObjects("Chart 1").Activate

 

[kidwispa]

Yes, you have to select the chart before running the macro. If you know what the chart name is ahead of time, then use the code to select the chart first.

The sheet that the chart is on is called "2011 Outcomes"... where (and how) would I put that into this code?

Thanks again for all your help



EDIT - Just saw your example - thanks again!

 

[Jeffrey Smith]

I recorded a macro to select the chart. Do the same and paste it in the top part of the code.