[PivotTable VBA]set different interior colors for column field, by leading pivot items

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
376
Hi,

The example here has been simplified.
Sample File︰Test.xlsx (16.57KB) - SendSpace.com

Suppose a PivotTable has already been created with its row fields, column fields, data fields chosen. The result is exactly what the image shows.

The column fields has 2 fields︰"Sex"being position 1, "Region" being position 2.

I hope that, for each pivot item of "Sex"(i.e. "M" & "F"), its occupied label ranges and also ranges of its sub-column field's items will be colored uniquely for identification.

In this case,
- range "B4:D5" will be colored with, say Yellow
- range "E4:H5" will be colored with, say Pale Red

I attempted the following code︰

VBA Code:
    Dim PT As PivotTable
    With ActiveSheet
        Set PT = .PivotTables("Test")
        PT.PivotFields("Sex").PivotItems("F").LabelRange.Interior.Color = vbYellow
    End With

However, only range "B4:D4" is colored Yellow. How do I also make range "B5:D5" also Yellow﹑

I hope I have expressed myself well.

Many Thanks.
 

Attachments

  • S.png
    S.png
    39.7 KB · Views: 9

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,181,757
Messages
5,931,869
Members
436,805
Latest member
waseem abbas

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 MrExcel.com.
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 "mrexcel.com".
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
Top