Conditionaly formatting a pivot table using a macro?


New Member
Jan 18, 2005
Hi all

I swear, Microsoft should include an archive of this site as part of their help files with Excel. You guys are the best! :)

However, I am not so sure you can help me with this one, but I will place it before you for your minds to tinker with.

I have a Pivot Chart that displays the setting of a test machine on the Y axis, and the machine number and date on the X axis. There is also a selection box to choose cable types and groups over the chart.

It all works rather well, apart from one glaring fault.

To the right of the chart, there is a Legend with different colours refering to different test settings. My manager wants the colours to remain constant in relation to the setting number, regardless of what changes are made to the display settings of the Pivot Chart.

As it stands, if you set it to display all results, across all dates, and across all cable types, the Legend says that setting 653 is Pink. When you adjust the chart to limit the dates, setting 653 will change to something else.

I wrote a macro that goes through each colour to change it to the right setting, but that doesnt work, as the amount of entries in the legend changes according to the parameters of the chart.

So effectively, to cut a long story short, I need something that goes to the effect:

If LegendEntry1 = SettingNo4 Then Color = Green
If LegendEntry1 = SettingNo5 Then Color = Blue

etc, etc for each of the 6 potential variations of colour, setting and legend position.

Can anyone shine any light on the subject for me?



Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Well. I took the example code from a Mr Excel archive article, and it seems to fit what I want to do, but in a different way.

The code relates to a pivot chart that displays information in a pie chart. The colors of the pie chart need to remain constant over multiple changes and updates.

The code is:

Sub ColorPieSlices()
' Copyright 1999
' This macro will re-color the pie slices in a chart
' So that slices for a specific category are similarly colored
' Select the chart before calling the macro
' Find the number of legend entries for the X axis in this chart
    NumPoints = ActiveChart.Legend.LegendEntries.Count
    ' Loop through each pie slice
    For x = 1 To NumPoints
        ' Save the label currently attached to this slice
        If ActiveChart.SeriesCollection(1). _
            Points(x).HasDataLabel = True Then
                SavePtLabel = ActiveChart.SeriesCollection(1) _
            SavePtLabel = ""
        End If
        ' Assign a new data label of just the point name
        ActiveChart.SeriesCollection(1).Points(x).ApplyDataLabels Type:= _
            xlDataLabelsShowLabel, AutoText:=True
        ThisPt = ActiveChart.SeriesCollection(1).Points(x).DataLabel.Text
        ' Based on the label of this slice, set the color
        Select Case ThisPt
            Case "653"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 1
            Case "53"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 2
            Case "6"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 3
            Case "5"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 4
            Case "4"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 5
            Case "3"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 6
            Case Else
                ' Add code here to handle an unexpected label
        End Select
        ' Return the label to it's original pre-macro state
        ActiveChart.SeriesCollection(1). _
            Points(x).DataLabel.Text = SavePtLabel
    Next x
End Sub

The original link for this code is:

Now, I have modified this code where I can, but a lot of it remains a mystery, as I do not know the correct syntax.

Would anyone please help me on this? I know that this is potentialy very easy, its just a syntax matter.


Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back