Excel 2007 : Relative Conditional Formatting in a PivotTable

[triskaidekaphobia]

Hi,

I am struggling to work this out:-

If the value in C5 (which is in a PivotTable) is greater than the value in E5 (which is NOT in the PivotTable) then I want C5 to be filled in a certain colour. This needs to apply to all cells in column C, relative to the corresponding cell in column E, all the way down to E288. The values in column E are VLOOKUP references but I'm assuming this doesn't matter.

I can't set the Conditional Formatting in C5 to ">$E5" and use the Format Painter because C5 is in the PivotTable. And obviously if I select the whole column it sets the reference to ">$E5" for all cells.

How do I do it?

Many thanks
Tom

[SCLai]

Welcome to the forums!

Regarding your question, you may want to try doing this:
Select Cell C5 (Pivot Table Cell)
1.) Go to conditional formatting
2.) Change "Cell Value Is" to "Formula Is"
3.) =C5>E5
4.) Set your formatting
5.) After you are done, select Cell C5, Copy.
6a.) Select the whole of Column C (by clicking on the Column Header "C")
6b.) or if you want it to be only till C288.. Click C5, drag till C288
7.) Right Click > Paste Special > Formats
This should give you what I think you are asking for.

Hope this helps!

SC

[triskaidekaphobia]

Hi SC,

Pasting over a selection of cells give an error about not being able to move part of a PivotTable or insert rows or columns, but I have re-organised the spreadsheet so that I can paste over the entire column, which works a treat.

Many thanks for your help
Tom