Conditionaly formatting a pivot table using a macro?

[fla5hbak]

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?


Thanks

Tref

 

[fla5hbak]

Anyone? Surely this isnt that difficult....

 

[fla5hbak]

Please? Pretty please?

 

[fla5hbak]

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 MrExcel.com
' 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) _
                    .Points(x).DataLabel.Text
        Else
            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:

http://www.mrexcel.com/tip027.shtml



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.

Thanks


Trefor[/code]