Color Pivot Field Labels Code Needs Minor Tweak

boldcode · Aug 17, 2011

Hello Everyone,

I need a fix for the following code that should change the background color of the pivot field "Year" labels ("2010", "2011", etc....) to grey. Since the pivot table can expand from year 2010 to 2020 or beyond, I having a hard time to code this macro just right.

Code below:

Code:

heets("Rates").Select
      Range("A5").Select
      ActiveWorkbook.ShowPivotTableFieldList = True
  With ActiveSheet.PivotTables("Inflation_Rates").PivotFields("Year")
          .Orientation = xlRowField
          .Position = 2
          .Subtotals(1) = False
      End With
  End Sub

Here what the Pivot Table looks like; I want year labels 2010 - to the last column (the year labels can contract and expand) of year labels to be colored grey (I hide rows 6-8 because it makes the pivot table look better):

<table height="509" border="0" cellpadding="0" cellspacing="0" width="1036"><colgroup><col style="mso-width-source:userset;mso-width-alt:1865;width:38pt" width="51"> <col style="mso-width-source:userset;mso-width-alt:3949;width:81pt" width="108"> <col style="mso-width-source:userset;mso-width-alt:3328;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:4461;width:92pt" width="122"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt;width:38pt" height="21" width="51">
</td> <td colspan="6" class="xl66" style="mso-ignore:colspan; border-right:1.0pt solid black;width:393pt" align="center" width="524">Rates</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21">Row 5</td> <td class="xl90">Column A</td> <td class="xl90">Column B</td> <td class="xl90">Column C</td> <td class="xl90">Column D</td> <td class="xl90">Column E</td> <td class="xl90">Column F</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 9</td> <td class="xl69">Product</td> <td class="xl70">Type</td> <td class="xl70">Name</td> <td class="xl70">Org</td> <td class="xl71">2010</td> <td class="xl72">2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 10</td> <td class="xl73">PDF</td> <td class="xl74">Masa</td> <td class="xl75">Money</td> <td class="xl75">JA</td> <td class="xl76">$0.02 </td> <td class="xl77">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 11</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl81">HC</td> <td class="xl82">$0.02 </td> <td class="xl83">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 12</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl81">RC</td> <td class="xl82">$0.02 </td> <td class="xl83">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 13</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl81">MM</td> <td class="xl82">$0.02 </td> <td class="xl83">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 14</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl75">Theory</td> <td class="xl75">JA</td> <td class="xl76">$0.30 </td> <td class="xl77">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 15</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl81">HC</td> <td class="xl82">$0.30 </td> <td class="xl83">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 16</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl81">RC</td> <td class="xl82">$0.30 </td> <td class="xl83">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 17</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl81">MM</td> <td class="xl82">$0.26 </td> <td class="xl83">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 18</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl75">Resources</td> <td class="xl75">JA</td> <td class="xl76">$0.00 </td> <td class="xl77">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 19</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl81">HC</td> <td class="xl82">$0.00 </td> <td class="xl83">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Row 20</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl81">RC</td> <td class="xl82">$0.00 </td> <td class="xl83">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" height="21">Row 21</td> <td class="xl84">
</td> <td class="xl85">
</td> <td class="xl86">
</td> <td class="xl87">MM</td> <td class="xl88">$0.00 </td> <td class="xl89">
</td> </tr> </tbody></table>

Color Pivot Field Labels Code Needs Minor Tweak

boldcode

Active Member

Excel Facts

Similar threads

Forum statistics

Share this page

Color Pivot Field Labels Code Needs Minor Tweak

boldcode

Active Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock