Preserving pivot table conditional formats

[PaddyD]

Hi all,

Dumb question for the day(the width of columns notwithstanding):

I have a pivot table with start date, end date & period in the rows, units calculated in the columns. I can write a conditional format to highlight any of the unit cells if period falls between the start & end dates & the unit cell is 0 or blank, but obviously this disappears when the table gets refreshed.

Any suggestions on how I could go about preserving the formatting for the table?

As ever, all help gratefully received,
Paddy

[Mark W.]

As the warning says, "Conditional formatting cannot be preserved in PivotTables...".

If I am to offer some alternatives I need to better understand the relationship among your fields, 'start date', 'end date' and period; what you're trying to "show" in your PivotTable.

A small amount of sample data would be helpful.

[ This Message was edited by: Mark W. on 2002-05-22 12:21 ]

[PaddyD]

OK - to the details.


Say the row values for column A hold account numbers, col b start dates & col c end dates. The analysis variable in the pivot table is 'units', with the remaining columns totalling the units consumed per period (say month).

So - an account may have a start date of 01/01/02, an end date of 31/05/02 and the sum of units consumed in Jan, Feb, March etc in columns D onwards.

For each account, the current conditional format checks the units consumed in each period & highlights zero consumption if the period falls between the start & end date.

At the moment, I'm picking up the old conditional format, refreshing the pivot & then copying it back. Not exactly difficult, but inelegant.

I will be going to buy a VB for dummies or equivalent at the weekend (unless anyone has better recommendations???), but until then....

As ever, all help gratefully received,
Paddy