Pivot Table Conditional Formatting for Minimum Value falls apart with Filters

[MichaelHurd]

Hi Mr Excel board, been using you for a few weeks now.

I have been tasked with creating a Pivot Table each week for our buyers that will highlight the lowest price from a vendor for each item. I used the solution found at Highlight the lowest and highest value in an Excel row or column - TechRepublic to accomplish this more efficiently with conditional formatting rather than the long row by row way that had been done before. I had a values tab at the end of the table list the Minimum of the Costs and with conditional formatting had the values in each row compared to this value. In this case I selected everything and had the rule be when c6 =$BU6 highlight the cell for that row. It worked.

The problem is they want to be able to filter by warehouse and buyer. When that's done the columns collapse as not all vendors are available to each warehouse and buyer so the Values column showing Minimum Cost is moved, so $BU becomes a blank cell and now every blank cell is highlighted in the table when a filter is used.

I got going a bit with this thread, http://www.mrexcel.com/forum/excel-questions/656059-conditional-formatting-pivot-table.html but then I lost the way as I'm still new to a lot of these advanced Excel functions.

Thanks so much in advance for your help as well as the help you've given me in the past.

I'm using Excel 2007 on Windows 7. No Administrator access so running some of the very useful Add-ins has been cumbersome.