Formatting background in Pivot Table

[#1]

I have a Pivot Table with 4 levels.  I have successfully figured out how to format each of the different Dimensionalities by doing this:

=

if(Dimensionality() = 1, RGB(46,72,101),
if(Dimensionality() = 2, RGB(73,115,160),
if(Dimensionality() = 3, RGB(141,170,203),
if(Dimensionality() = 4, RGB(186,204,224)
))))

It looks great when all levels are fully expanded. But when each level is "collapsed", the rows ALL become the color of the level to which it is collapsed.  So when the entire pivot is collapesed (in this case), the remaining rows are all RGB(46,72,101).

Is there a way to conditionally format the Dimensionality based on the level of DImensionality that is "expanded" or "collapsed"?  Does that make sense?  In other words, I ONLY want the SUBTOTAL rows highlighted, not the "regular" rows.

Thanks,  Dan

[#2]

Please attach example, its will Help meqothes to help you.

I think It Suppose to work when it close.

[marcus_sommer]

I'm not sure if it's worked but I would try to extend the color-conditions to queries for rowno() or rowno(total) or a count(distinct Dim1&Dim2&Dim3&Dim4) maybe wrapped in an aggr-function.

Maybe you could find a suitable workaround if you used several objects and denied the collapsing within the options.

- Marcus