I have created a pivot table in Excel 2007 that sums of dollar and unit sales
for two time periods and then I have created formulas in the pivot table to
calculate average price for the two time periods.

I now want to set up conditional formatting based on the average price for
the whole line. If price on left is higher than the price on the right, make
the font red for the whole row. If the price on the left is lower than the
price on the right, make the font blue for the whole row.

Worked the first time when I set these up, but if I hit refresh, now just
the first column has the conditional formatting and the rest of each row is
back to normal black text. Even if I open and review the conditional
formatting, it still is there, but the only option for now is to not save the
file, close it, and re-open it to see the formatting still.

I chose "Use a formula to determine which cells to format" and have entered
two in it. I have =$AF6<$AG6 for "Format values where this formual is true"
and applies to =$AA$6:$AA$58.

Any help would be greatly appreciated and let me know if there are any
questions.
--
Peter

I even have updated, but still not resolved problem.

Down below it should applying to =$AA$6:$AG$58, but as soon as I hit refresh
for the pivot table it changes it back to the =$AA$6:$AA$58, which is just
the first column. I tried again in a fresh spreadsheet and still have this
problem. Refresh with Excel 2007 changes what has been defined as applies
to. I tried using relative cell reference and will not allow me to do this.

Again, any help would be appreciated.
--
Peter


"peter" wrote:

> I have created a pivot table in Excel 2007 that sums of dollar and unit sales
> for two time periods and then I have created formulas in the pivot table to
> calculate average price for the two time periods.
>
> I now want to set up conditional formatting based on the average price for
> the whole line. If price on left is higher than the price on the right, make
> the font red for the whole row. If the price on the left is lower than the
> price on the right, make the font blue for the whole row.
>
> Worked the first time when I set these up, but if I hit refresh, now just
> the first column has the conditional formatting and the rest of each row is
> back to normal black text. Even if I open and review the conditional
> formatting, it still is there, but the only option for now is to not save the
> file, close it, and re-open it to see the formatting still.
>
> I chose "Use a formula to determine which cells to format" and have entered
> two in it. I have =$AF6<$AG6 for "Format values where this formual is true"
> and applies to =$AA$6:$AA$58.
>
> Any help would be greatly appreciated and let me know if there are any
> questions.
> --
> Peter