Formatting subtotals - help pleassseeee

[dmwilson38550m]

HI,
I wonder if anyone can help. I have a large report that I have subtotalled but I would like to apply formatting to the subtotals. Specifically I would like to :

1. BOLD the subtotalled lines including the GRAND TOTAL.
2. Shading - shade grey the subtotal lines eg columns D,H,I,J,K,L,M
3. Shading - shade yellow the GRAND TOTAL line (eg columns D,H,I,J,K,L,M
4. Double underscore the GRAND TOTAL line eg columns D, H,I,J,K,L,M
5. Single underscore the subtotalled lines.

Any advice would be greatly appreciated. There are probably about 8 subtotalled lines and 1 grand total line.

Many thanks,
David

 

[Michael M]

Hi David

1. Do the Subtotal and grand total lines change position or are they constant
2. Have you tried using the macro recorder to to do this.
3. Post a sample screen shot of what you have / want

Have a a look at the 2nd line of my tag for a HTML maker to allow you to do item 3.

 

[Peter_SSs]

David

Try this in a copy of your workbook. It assumes that your subtotalled data starts in cell A1.

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> Format_Subtotals()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    ActiveSheet.Outline.ShowLevels RowLevels:=2<br>    <SPAN style="color:#00007F">With</SPAN> Range("A1").CurrentRegion<br>        <SPAN style="color:#00007F">With</SPAN> .Offset(1).Resize(.Rows.Count - 2).SpecialCells(xlCellTypeVisible)<br>            .Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>            .Font.Underline = xlUnderlineStyleSingle<br>            .SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 15<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> .Rows(.Rows.Count)<br>            .SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 36<br>            .Font.Underline = xlUnderlineStyleDouble<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    ActiveSheet.Outline.ShowLevels RowLevels:=3<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[dmwilson38550m]

Hi,

Thanks for that - it works perfectly.

Regards,
David

 

[Peter_SSs]

Hi,

Thanks for that - it works perfectly.

Regards,
David

David

Thanks for the feedback. It helps greatly when there is a clear, concise description of the data and requirements - like you gave.