Macro to copy/paste pivot table conditional formatting

[Walky]

Hello,

I am using Excel 2003 to generate several pivot tables in which I use conditional formatting on several columns.

Unfortunately, when the size of the pivot tables change, the formatting remains on the same cells as previously, so some data (new or moved) is not highlighted.

The "Enable Selection" option is active and I use it to select the populated cells of the column to which each conditional format is applied (different for each column).

I need some help to automate the "workaround" I use:

- I delete all conditional formats from row 6 downward on my 1st pivot table: row 5 holds the 1st line of data and the conditional formats I'd like copy downward

- I "select" the header of the 1st column that has the conditional format I'd like to copy downward and apply it to the populated cells of the pivot table (the "Enable Selection" option). This is repeated for several columns.

- I go to the 2nd pivot table and start over again... 5 tables in all and 16 formatted colums.

Unless I missed an already published solution (I did several searches) or if someone has a better solution (welcome), could a macro genius give me a hand? I found macros that are not specifically meant for pivot tables (not using the "Selection"), so useless (IMHO) in this case.

By the way, the conditional formats are different from one column to another, but identical on 4 of the 5 tables. the 5th table has one additional column with conditional formatting.

Thanks in advance,

W.

 

[Desu Nota from Columbus]

Could you record a macro of:

1) Selecting entire column
2) Apply the conditional formatting
3) Removing conditional formatting from rows 1-5 (if that is what you need blank)
4) Repeat steps 1-3 for each column




Then you add this macro code to your existing macro (making the appropriate worksheet name changes in the code if necessary)



This is the only way i know how to work around your problem.

 

[Walky]

Hi,

Thanks for your reply.

I have cells into which I enter variables which define when the conditionals formats will be active and I wasn't convinced the recorded macro would point to those cells... I thought it would store the defined variable and not change it accordingly.

But I tried it (not tested extensively) and it seems to be working, refering to the cells containing the link to where the variables are defined.

Good idea, thanks for bringing me back to the "basics"

Regards,

W.

 

[Desu Nota from Columbus]

Glad I could at least spark an idea that could pan out.