I have a pivot table and I want to have the format apply to any cell in the data area, regardless of the configuration of the column and row fields. I want the formatting to fit whatever the table displays. Any help would be appreciated.
By Conditional Formatting, are you referring to the Data Area being formatted uniformly whatever the configuration, or are you referring to formatting cells depending on their values (like range Conditional formatting)?
If it's the first one, consider using a custom PivotTable Style.
Click on your Pivot > Ribbon > Design tab > in the PivotTable Styles group, click the down arrow of the scroll bar >
New PivotTable Style.
Once you have saved a custom PivotTable Style to the PivotTable Style Pallette, you can reuse it in that Workbook.
If you want to reuse it in another Workbook, you can temporarily copy a Pivot that is using that style into your other Workbook. Your PivotTable Style Pallette will then appear in that Workbook's Pallette.
Your question relates to this part of the description:
"If you change the layout of the PivotTable report, by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained, as long as the fields in the underlying data are not removed."
In my experience, this is not always true (or I might be misinterpreting the description).
PivotTable CF that is applied with "Scoping by value field" or "Scoping by corresponding field" seems to exhibit this behavior:
filtering: CF Maintained
hiding levels: I'm not postive what that means. CF is not Maintained when a field is deselected then reselected in the Field List.
collapsing and expanding levels: CF Maintained
moving a field: CF Maintained if changing the Position of a field within the same PivotTable area; Not Maintained if moved to another area.
Based on that (and I'd appreciate a correction if I have any of that wrong), I believe you'll need to use a VBA approach to achieve your desired result.
Here are links to two examples that take very different approaches.
I found what I was looking for. Home>Styles>Conditional Formatting>Manage Rules. Once here, there is a drop down at the top of the dialog box: "Show formatting rules for :". I selected "This PivotTable".
Then I added a new rule and assign it a cell in the pivot table, save it and close the rule. I then go back and edit the rule and at the top of the edit dialog box, there is a box that has a range selection box and three option buttons:
All cells showing "XXXXXXX" values
All cells showing "XXXXXXX" values for "YYYYY" and "ZZZZZZ"
"XXXXXXX" = values in the data area of the PivotTable.
"YYYYY" and "ZZZZZZ" (for this example) are the Row and Column Labels.
If I select the second option, it applies the format to all values in the data area, regardless of configuration.
...allows you to find all the existing rules applied to your PivotTable, but doing those steps doesn't apply rules to the PivotTable.
This method should still have the shortcoming of not maintaining CF if a field is deselected-reselected or moved to another area of the PivotTable. If you don't need the capability to maintain the CF for those events, then your approach should work fine and there's no need for any VBA.
I have a pivot table monitoring stock levels for products- (I am using excel 2013). I want to apply conditional formatting to the the Row header "Sum of Stock cover in Days" wherein if "Sum of Stock cover in Days" is less than "Sum of Min Cov." it will colour red, if "Sum of Stock cover in Days" is greater than "Sum of Max Cov.", it will colour yellow and if "Sum of Stock cover in Days" is between "Sum of Min Cov." and "Sum of Max Cov." it will colour to green.
What will be the steps to do this using the row headers so that when data refreshes it wont lose the conditional formatting ? Note that Row headers are stuctured consistently when raw data is dumped.
By the way, i dont have any background in using VBA or macros.