conditional formatting a pivot table

[vapidgeezer]

In columns A through G I have a pivot table that totals the number of complaints by product for the last 6 months. In column H I have a statistically derived threshold level.

A B C D E F G H
Jan Feb March April May June Threshold
Product 1 0 5 0 0 1 0 1.5
Product 2 0 2 0 0 9 0 2.5

I would like to conditionally format each cell B through G for that row based on the value in column H. Red if above the Threshold Green if below.

Two part Question: 1) The spreadsheet has some 400,000 products so I do not want to create the conditional formatting for each row separately. I do not understand how to set the relative references so that they formating rules can be copied down to subsequent rows. 2) I would like to be able to filter the pivot table and have the conditional formatting update based on the filter.

My company uses Excel 2007 Windows XP. I have access to excel 2011 on a Mac also.

 

[Andrew Poulsom]

If the table you posted is in the range A1:H3, select B2:G3 and use the conditional formatting formulas:

=B2 < $H2

=B2 > $H2

 

[vapidgeezer]

My version of excel (2007 windows xp) gives me an error that says that
"You cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets"

I can use an absolute format one row at a time but I have 400,000 some rows to format which is a pain manually. Do I need to set up a macro?

 

[Andrew Poulsom]

Just use a fill colour.