Advanced conditional formatting and Pivot Tables

[kyrgyzstanart]

Hello,

Excel version - Excel 2007.

Problem statement:

Need to apply conditional formatting on the Pivot table data area (Values area) based on one of the row values.

For example, in the attached picture (as well as the XLSX file), I have 3 differnet Product Families: Fruit, Vegetable and Misc; in each of these families there are several products (Fruit: Apple, Banana, Orange; Vegetable: Carrot, Onion, Patato; Misc: Milk and Vodka). For each product there are stock levels.
I would like to apply conditionnal formatting on each stock value for each product based on their Family. For Fruit everything that is above 50 is red, BUT for Vegetable everything above 100 is red, and for MISC everything that is above 500 is red.

Is it possible to do that?

[DonkeyOte]

I guess it will all boil down to how complex your real PT setup is... attached is 2007 macro enabled workbook with a VB Routine installed that will update the color index of the databodyrange as and when the PT is refreshed according to your rules per category... as such there is no Conditional Formatting applied per se at cell level (CF is volatile).

In reality this will probably need tweaking but for the purposes of answering the question posted and providing an example this should give you some pointers.

[kyrgyzstanart]

Hi DonkeyOte,

Thanks for the VBA code. However, it means that every time I change the Characteristics to be visible in row the conditional formatting may not follow. Meaning that if I put the Product Family Field into the Report Filter area, all conditional formatting will be gone. It means that in order for this conditional formatting to work I will have to leave the "Product or Family" Field in the "Row Labels" part of the pivot table.

Is my inderstanding correct?

Thank you again!

Moreover, DonkeyOte, I am not a VBA newb but no Guru either, and unfortunately I don't understand very well the code. Could you, please, point me to the material that would help me understand it and adapt to my needs?

[DonkeyOte]

Yes, as I said the example was based on your initial setup - there was no detailing as to how "flexible" it need be... if you moved the criteria to be a Report Filter (old PAGE Field) then unless said filter was set to be singular selection (ie one of your 3 choices) it would be impossible to differentiate between the 3 groups given resulting values.... ie if set to All or Multi-Selection you would have no means by which to determine which field a value related to - be it Fruit, Misc, Vegetable and thus no means by which to apply the interior colour index... this would hold true for standard Conditional Formatting rules also (ie not just to the VBA routine).

I would change the code I provided previously ever so slightly such that you could move the Row Label position of the "Product Family" field without impacting the resulting formatting... eg:

Please Login or Register  to view this content.

[kyrgyzstanart]

Thank you again!

Moreover, DonkeyOte, I am not a VBA newb but no Guru either, and unfortunately I don't understand very well the code. Could you, please, point me to the material that would help me understand it and adapt to my needs?

[DonkeyOte]

I'm afraid I'm self taught so I can't refer you to anywhere/anything specific ... I don't do much on the PT side of things so the above is probably not very good code anyway... check out the Object Browser in VBEditor as this will show the various properties available for different objects etc ... other than that it's a case of Google, resources such as this and of course the less favoured Trial & Error

[kyrgyzstanart]

thanks, m8, i will do the necessary digging.