Conditional Formatting vs Pivot Tables [SOLVED]

[ianb]

If I have a grey background and a pivot tables over the top.


1. Pivot is white.

2. Background is grey.


Conditional format ="" format grey should keep the background grey if the pivot table enlarges and then becomes smaller. yet it only does the left sixe of the pviot table.


Any ideas to a solution else when I interact with the pviot it shows white on the smaller criteria until i go back to the largest !!!


*Background needs to be grey and pivot needs to be white. how simple can it be !!! lol


Many thanks if any one has any ideas or a solution....

 

[jeffreyweir]

I don't quite follow. Conditional formatting works fine when I try it.


Say my pivot occurs in H7:I22 but might change in size. If I select the range say G4:N26 and select Conditional Formatting>New Rule>Use a formula to determine what cells to format and then type =G4 = "" and then click the Format button and choose a grey background then it works fine. Any time the pivot expands or contracts, the grey cells expand/contract accordingly.

 

[ianb]

It works fine in Excel 2003 in Excel 2007 and Excel 2010 it will only format all initially then when the pivot changes from large and then back to small the left side will format back as grey yet the right side will not.

 

[jeffreyweir]

I see what you mean. Initially on the sample I whipped up, when I made the pt smaller, the formatting worked, but when I made it bigger and then smaller again, it stopped working.


So it seems the pivot formatting completely overrides the contidional formatting in the same way that if you copy some cells from somewhere into a range containing formats, those formats get wiped. That's nasty.


I'm guessing that you're going to have to reapply the conditional formatting based on a macro using a Worksheet_PivotTableUpdate event handler.

 

[ianb]

Thats something to work upon. I will give it a go and if I find a solution will respond back again. Will be fun as the dashboard contains over 100 visible pivot tables. plus another 100+ hidden. Only need to do the visible ones... thanks for the advice.

 

[ianb]

Some thing like this.


Private Sub Worksheet_PivotTableUpdate(ByVal target As PivotTable)

Range("B47:C87").Select

Range("C47").Activate

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="="""""

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = -0.249946592608417

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("C81").Select

End Sub

 

[jeffreyweir]

Hmmm...with that many pivots, conditional formatting might really slow your workbook down. With that in mind, maybe you want to think about how much you really need those backgrounds to be grey. I'd be inclined to just leave them white, and turn gridlines off.


Also note that re your code above, you almost never need to select something to do something with it. And it's much faster to NOT select something.


Got to go to bed. Will check this thread tomorrow.

 

[ianb]

I have the whole of the dashboard designed in grey therefore must remain grey as a requirement


Did select this program above for each sheet. and does not go slow.


The one part I can not do is return next to the pivot table I have just selleted.


Range("C81").Select at the end is not the location of the e.g. pivot 10 that is around cell AE17


Any ideas how to have vba return back tot the last cell selected.


Thanks.

 

[ianb]

Using previous cell reference is one idea. jumps around...not smooth. would be just best if Excel did the conditional formatting as it did in 2003. this just does not happen in 2010.


Thanks for the idea. hoep this helps other people. would be intereted if any one has a better solution.


Private Sub Worksheet_PivotTableUpdate(ByVal target As PivotTable)


Dim PrevCell As Range

Set PrevCell = ActiveCell


Range("B47:w112").Select

Range("w112").Activate

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="="""""

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = -0.249946592608417

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("W112").Select


PrevCell.Select


End Sub