Bold Font an Insert SubTotal from Macro

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Bold Font an Insert SubTotal from Macro

Post by JimmyC »

Hans was kind enough to provide the code below..many, many moons ago:

Sub CreateSubtotals()
Dim wsh As Worksheet
Dim i As Long, lRow As Long
For Each wsh In ActiveWorkbook.Worksheets
wsh.UsedRange.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
lRow = wsh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lRow To 1 Step -1
If InStr(wsh.Cells(i, 5), "Total") > 0 Then
wsh.Cells(i + 1, 5).EntireRow.Insert
End If
Next
Next wsh
End Sub

Over the years, it has worked just fine---no surprise there with Hans' code.

When it inserts the total in column 5 it is in bold font...What I have been experimenting with is to also bold font the sub total it produces in column 4. I have been googling and checked this lounge for other posts...no luck. Is there a way to bold font the sub-total inserted in column 4 from this macro or, does it have to be a separate macro? I have headed down this road, but can't figure out how to "locate" the inserted sub-total and bold font it...I only want the subtotal in bold font---the other amounts in column 4 I want to remain in regular font.

I am using Excel xp and xp sp3 for an operation system. Thank you.
JimC

User avatar
HansV
Administrator
Posts: 78488
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Bold Font an Insert SubTotal from Macro

Post by HansV »

Immediately below the line that begins with If, insert this line:

wsh.Cells(i, 4).Font.Bold = True
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Bold Font an Insert SubTotal from Macro

Post by JimmyC »

Hans,
Thank you so much...90 minutes of googling and searching this forum...and an answer in minutes for this lounge....Thanks again.
Jim