Problem with removing background color on rows of a pivot table 1

[chriscboy]

HI,

I have a pivot table where I allow users to select particular rows to carry out a given task. When the user selects the rows , I highlight the rows in yellow.

Once the user is happy with the selection they then press another button to carry out the task. Using some more VBA code I deselect the highlighted rows, then update the data connected to the pivottable (in this case data in another sheet), and then I refresh the pivot table to reflect changes made to the underlying data. The problem is when this is all complete the rows are still highlighted in yellow!!

I have stepped though the code and the rows get unhighlighted before I update the data in the other sheet. But as soon as I issue the ActiveWorkbook.Worksheets("Contract Portfolio").PivotTables(1).RefreshTable command, the previously unhighlight rows turn yellow again! What gives?

If you go to

http://www.geocities.com/chris_crowhurst/excel/

and download the file pivotreport.xls that will give you the example I am working on.

To reproduce the problem, highlight two rows. Click on the "Select" button. The rows should turn yellow. Click on the Transfer button. Type in FS as the consultants intials, click OK. Type in Fred Smith as the FULL NAME, click Ok. Type in TEAM2 as the TEAM, click OK. Then you will see yellow highlighting go beserk!

All I want to do is remove all the highlighting after the transfer has been made.

Can anyone help?

 

[chriscboy]

Oh, I am using Excel 2000 BTW

 

[combo]

Looks like bug, however you do not clear all formatting.

For me works with (instead of xlPatternSolid):
loBackRange.Interior.Pattern = xlPatternNone

In case of troubles, you can clear 'Preserve formatting' option for whole pivot table, refresing a table will clear all user formats.

combo

 

[chriscboy]

Thanks,

How do you clear the "Preserve formatting" option for the whole pivot table using VBA code?

Chris

 

[combo]

With PT as reference to your pivot table:
PT.PreserveFormatting=False

You can do it in excel, right-click any cell of table, select table options and untick this property.

Anyway, it can influe behaviour of the table, I would rather use xlPatternNone for Interior.Pattern (together with Colorindex), as this clears all previous formatting).

combo

 

[chriscboy]

Thank you very much, its working very nicely now. A star for your good work!

Thanks again!

Chris