Formatting rows when using Subtotals

[DavidC]

I am preparing a macro for a report that includes subtotals using the subtotal command. However, it is difficult to look at, Excel does a lousy job of formatting it. Is there a way to automatically format the subtotal rows, so that it separates the groups a bit more nicely? Perhaps just to change the color of the subtotal rows? Thanks!

[DavidC]

Don't tell me I actually stumped everyone! Surely there is a way to format the subtotal rows?!?

[eliW]

On 2002-04-08 13:12, DavidC wrote:
Don't tell me I actually stumped everyone! Surely there is a way to format the subtotal rows?!?

I'd expxcted the *gurus* to comment on this question, but as no one did I'll try my best.

As the subtotal cells are actually formulas, use this to locate them and paint the cell.

You can include in your macro: Edit|Goto|Special|Formula to locate thease cells and then paint them or the entire row.

Hope this helps,
Eli

[fght]

On 2002-04-08 12:23, DavidC wrote:
I am preparing a macro for a report that includes subtotals using the subtotal command. However, it is difficult to look at, Excel does a lousy job of formatting it. Is there a way to automatically format the subtotal rows, so that it separates the groups a bit more nicely? Perhaps just to change the color of the subtotal rows? Thanks!

Display level 2 rows.
Go to Edit>Special>VisibleCells>Only.
Format as required.

[Tom Urtis]

DavidC:

Since you asked for an automated solution here's one. The code looks a bit lengthy at first but it formats using a union range, not just one row at a time, to save a little system effort. Modify for subtotal column and format options (color index, bold etc). Thanks to Celia from a while back with the foundation of this code.

By the way, regarding your second post as follow-up to your first less than an hour before, some of us have lives during the day and can't get to these questions until this lovely hour of the morning. But we do what we can to assist. Here's the code to get you started.

''''''''''''''''''''''''''

Sub SubtotalFormat()
Application.ScreenUpdating = False
Dim myCol As Range, cell As Range, RtoSel As Range
Dim LtoSel As String
Set myCol = Range(Range("A1"), Range("A65536").End(xlUp))
LtoSel = "Total"
For Each cell In myCol
If Right(cell, 5) = LtoSel Then
If RtoSel Is Nothing Then
Set RtoSel = cell
Else
Set RtoSel = Application.Union(RtoSel, cell)
End If
End If
Next
With RtoSel.EntireRow
.Interior.ColorIndex = 3
.Font.FontStyle = "Bold"
End With
[A1].Select
Application.ScreenUpdating = True
End Sub

''''''''''''''''''''''''''''''

[Larry]

Tom,

Where and how in the example you listed above could I insert a row after each subtotal row?

Any info will be greatly appreciated.

Thanks,

Larry

[fght]

On 2002-04-09 09:08, Larry wrote:
Tom,

Where and how in the example you listed above could I insert a row after each subtotal row?

Any info will be greatly appreciated.

Thanks,

Larry

Sub SubtotalFormat()
Application.ScreenUpdating = False
Dim myCol As Range, cell As Range, RtoSel As Range
Dim LtoSel As String
Range("A65536").End(xlUp).EntireRow.Insert
Set myCol = Range(Range("A1"), Range("A65536").End(xlUp))
LtoSel = "Total"
For Each cell In myCol
If Right(cell, 5) = LtoSel Then
If RtoSel Is Nothing Then
Set RtoSel = cell
Else
Set RtoSel = Application.Union(RtoSel, cell)
End If
End If
Next
With RtoSel.EntireRow
.Offset(1, 0).Insert
.Interior.ColorIndex = 3
.Font.FontStyle = "Bold"
End With
Range("A65536").End(xlUp)(0, 1).EntireRow.Delete
[A1].Select
Application.ScreenUpdating = True
End Sub

[Larry]

FGHT,

That worked for the first subtotal, but it stops after the first. What am I missing to have it insert a row after all subtotals?

Thanks,

Larry

[ This Message was edited by: Larry on 2002-04-09 12:26 ]

[fght]

On 2002-04-09 12:24, Larry wrote:
FGHT,

That worked for the first subtotal, but it stops after the first. What am I missing to have it insert a row after all subtotals?

Thanks,

Larry

[ This Message was edited by: Larry on 2002-04-09 12:26 ]

It works for me.

[Larry]