Modify outline styles

[agibsonsw]

Hello. Excel 2010.

I thought it was possible somehow to modify styles so that they would be applied when using the Subtotals feature, changing them from their default bold and/or italics and hopefully applying to the whole subtotal rows. I think they are called RowLevel_1, RowLevel_2, ColLevel_1, ColLevel_2. Has anyone achieved this, I cannot work out how?

[Rudi]

Personally, I would not use styles. I would recommend using conditional formatting as it would be much more dynamic.

I think the styles you are referring to are Pivot Table styles...?

Image 7.jpg

[HansV]

Although Excel will create style such as RowLevel_1 etc. if you tell it to apply styles to a subtotal/outline, it won't automatically apply those styles to other subtotals/outlines in the same workbook, so it appears to be a relatively useless feature.

[Rudi]

Hi Andy,

In my years of working with Excel I have never even seen that setting you referenced. (First, apologies - I now see what settings you refer too!)
After experimenting with them, they don't seem to do much at all though (as Hans mentions). I will stick to my advice to use C/F.

[agibsonsw]

Thank you both. Yes, I recall trying and failing before to get it to work. I attempted to first create styles of those names, and use Outline Settings to Apply Styles but it doesn't work.

Conditional Formatting would be a good, dynamic, solution. For the moment I've gone with a macro to collapse and format the outline:

Code: Select all

Sub Macro4()
    Application.ScreenUpdating = False
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Font.Bold = True
    'change the header row if necessary
    Range(Range("A1"), Range("A1").End(xlToRight)).Font.Italic = True
    ActiveSheet.Outline.ShowLevels RowLevels:=3
End Sub