Formatting SubTotals

[jacqui]

How do I format just my subtotals (created using Subtotals
method) so that they are in bold font?
I posted a similar question earlier but did not receive a
reply.
Have sussed the ClearOutline method by using
ActiveSheet.UsedRange.Select to ensure I select all my
data not just a subset of the range.
Any help with formatting is appreciated.
Many thanks
Jacqui

 

[Jim Becker]

Posted to the newsgroup with email copy (due to posting being 5 days old)...

Here's a neat trick that I picked up some time back. You can use the
Styles collection to format a Subtotaled selection.

Within Excel:
- Subtotal a worksheet
- Select the subtotaled range, then Data -> Group & Outline -> Settings
- Click Apply Styles. This will add at least two custom Styles to the
Styles collection. These are named RowLevel_1, RowLevel_2, etc.
RowLevel_1 corresponds to the Grand Total row; RowLevel_2 to the
first level subtotals, etc.
- You can then modify the style to achieve the desired formatting.
Format -> Style, select RowLevel_1, then Modify.
- Note that Styles is a workbook level collection, so if you apply styles
to multiple subtotaled ranges, and then modify the formatting, it will
be applied to each of them.

The VBA coding I use for this functionality is:

With ActiveSheet
.UsedRange.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7)
.UsedRange.ApplyOutlineStyles
.Outline.ShowLevels RowLevels:=2
End With

With ActiveWorkbook
.Styles("RowLevel_1").Font.Bold = True
.Styles("RowLevel_2").Font.Bold = True
End With

Hope this helps,
James dot Becker at NCR dot com
~
~
~
:wq!