• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Format Pivot Table Row

wilkies0106aw

New Member
All,


I have a pivot table which connects to external data and refreshes on opening. I can conditional format cells based on a value, yet how can I conditional format entire rows based on a cell value?


I want to actually hide the referencing column/cell value, but still somehow be able to apply the CF to the entire row.


Thanks


W
 
Select the entire row, and when you apply the conditional format, make the column reference absolute. So, say we want the entire row 2 to be formatted if cell in col B = 5. formula would be:

=$B2=5


That wall, all the cells in that row know to look in col B, but when you copy down to a new row, the relative reference to row 2 changes and becomes row 3.
 
Thanks for that, it seems to work. Although the conditional formatting doesn't seem to extend across the row labels too.


If I change this apply this option to include the label row. I receive the following error: cannot apply a conditional format to a range that has cells outside of a PivotTable data range. Make sure that all the cells in your selection are inside of the PivotTable region.


Since I'm only selecting the PivotTable data, not sure what is going on.
 
Was there a solution to this? I have an inventory sheet, each part has a serial number, and gets a +1 entry when received into stock and a -1 when taken out. I've put a pivot on it so I have a PT with Part, Serial Nr, Current Qty (Sum of Qty). I.e. every serial nr that is in stock has a +1, anything that has gone in and then been removed has a +1 and a -1, so Sum of Qty is 0. I want to Grey out the serial numbers (Row Label 2) and the Qty (Value) when the Qty is 0.

I've tried just applying the conditional formatting to $B:$C, and it works - until I refresh the PT. Then the range that this rule applies to gets the pivot table range removed.

I can apply a conditional format to the Sum of Qty, and in the Conditional Formatting range shows "Sum Of Qty", but I can't get it to apply to the label also.

[SOLVED] - as I was writing this post I was trying a few things, and I tried one rule on the sum of Qty and another rule on just $B:$B - this doesn't get corrupted when the pivot refreshes.
 
Back
Top