Subtotals

[mwhit]

Hello,

Is their an easy way for me to apply formatting to subtotals? I will like to bold on each subtotal.

Thanks!

 

[Smitty]

Not really; the format painter is probably the easiest way, but it can be time consuming.

i code it:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> HighlightTotals()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
            <SPAN style="color:#00007F">If</SPAN> Right(c, 5) = "Total" <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">With</SPAN> Range(c, c.Offset(, 65)).Font
                    .ColorIndex = 5
                    .Italic = <SPAN style="color:#00007F">True</SPAN>
                    .Bold = <SPAN style="color:#00007F">True</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

That looks through the Subtotals on the Totaled column. If it's a Total the row is Bolded & Italicized.

Hope that helps,

Smitty

 

[Tom Urtis]

Unless I'm mistaken it's fairly easy with conditional formatting, no VBA required. If you think ahead and know that for example, column A contains names, and column B contains their sales figures, and you want to subtotal for Sum at each change in name, that means you know that "Total" will be the five right-most characters in column A, where in the corresponding cell in column B will be the subtotaled sales amounts.

Therefore, assuming your field header row is row 1, select A1, press Ctrl+Shift+*(8) to select the current region, with A1 as the active cell.

Now from the worksheet menu click Format > Conditional Formatting, select Formula Is from the drop-down, and enter
=RIGHT($A1,5)="Total"
Note the $ character in front of column A in that formula.

Click the Format button, select the Font tab, choose Bold in the Font style pane (because you said you wanted the subtotaled rows to be Bold), click OK, then OK.

Finally, do the subtotal and your sheet will be conditionally formatted for bolded cells in the subtotal rows.