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

 

[Jeffrey Smith]

I made a mistake in the fill for Series 3. Please use this one

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 ActiveChart.SeriesCollection(3).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

 

[kidwispa]

Hi Jeffery,

Here is what I finished with (and it works!)

Sub CWChartFormat()
'
' CWChartFormat Macro
'
  Dim SCount As Integer
  
 Sheets("2011 Outcomes").Select
 ActiveChart.ChartArea.Select
  
  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, 255, 255)
        .Transparency = 0
        .Solid
    End With
   End If
    
End Sub

Thanks to both Andrew and yourself for your help with this!

 

[kidwispa]

Hi,

The code below works when all three series have value against them (Paid,Pending,Rejected) however I have just updated my data and now there is only "Paid" and "Rejected", but the colour for "Rejected" has now become orange!

Is it possible to add in a condition that states:

-when there are only 2 data series, then the 2nd one should be red
-when there are 3 data series, the 2nd should be orange and the third red

???

I wouldn't know where to begin with this so any help would be great!!!

Sub CSAColourUpdate()
  Dim SCount As Integer
 
 Sheets("Claim Success By Agent").Select
 ActiveSheet.ChartObjects("Chart 1").Activate
 ActiveChart.PlotArea.Select
 
  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 ActiveChart.SeriesCollection(3).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
   End If
 
End Sub

 

[Andrew Poulsom]

Why don't you add the formatting to the code I posted? I couldn't do it because I didn't have access to Excel 2007 at the time.

 

[Jeffrey Smith]

Seems obvious to me. You Just need to add IF statements. Put comments in your code to help you remember colors and other stuff. Play with the code by making a copy and seeing what will work. Record bits of macro and paste it into the code you have; it's easier than trying to learn from scratch. I won't be posting to this thread anymore. Good luck.

Jeff

Sub CSAColourUpdate()
  Dim SCount As Integer
Sheets("Claim Success By Agent").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
  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
        If SCount = 2 Then
          .ForeColor.RGB = RGB(255, 0, 0)   'Red
        ElseIf SCount = 3 Then
          .ForeColor.RGB = RGB(255, 204, 0)   ' Orange
        End If
        .Transparency = 0
        .Solid
    End With
  End If
  If SCount > 2 Then
    With ActiveChart.SeriesCollection(3).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
   End If
End Sub

 

[kidwispa]

Why don't you add the formatting to the code I posted? I couldn't do it because I didn't have access to Excel 2007 at the time.

Hi Andrew,

I've done as you suggest and now have the following:

Sub CSAColourUpdate1()
    Dim i As Long
    
    Sheets("2011 Outcomes").Select
    ActiveChart.PlotArea.Select
        
    With ActiveChart
        For i = 1 To .SeriesCollection.Count
            With .SeriesCollection(i).Format.Fill
                Select Case .Parent.Name
                    Case "PAID"
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(0, 128, 0)
                    .Transparency = 0
                    .Solid
                    Case "PENDING"
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(255, 204, 0)
                    .Transparency = 0
                    .Solid
                    Case "REJECTED"
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(255, 0, 0)
                    .Transparency = 0
                    .Solid
                 End Select
            End With
          Next i
        End With
End Sub

however when i run it I get run time error 438 - "Object doesn't support this property or method'??? Can you see what I'm doing wrong?

Thanks again

 

[Andrew Poulsom]

You've got Format.Fill whereas I had just Fill. So I think you need:

Select Case .Parent.Parent.Name

 

[kidwispa]

That works great - thanks again Andrew!