Formatting of Menu/Data/Subtotals

[shdawson]

Hi,


Not sure this is the place to ask, but will start here.

Have a massive report that would be nicely served by the Subtotals option on the menu. My question is about formatting of that data. After the *many* rows subtotal, would be nice to bold those rows. The said row will change with each report run, so it is a manual thing now.

Is there a cool plugin out there on the web or something that would assist with the formatting of the subtotaled information? Or, anther trick?


Thank You,
SHD

 

[VoG]

But maybe this would be more flexible

Sub BoldSbttls()
Dim i As Long, LR As Long, r As Range, c As Integer
On Error Resume Next
Set r = Application.InputBox("Click in the column containing the word Total", Type:=8)
If r Is Nothing Then Exit Sub
On Error GoTo 0
c = r.Column
LR = Cells(Rows.Count, c).End(xlUp).Row
For i = 3 To LR
    If Cells(i, c).Value Like "* Total" Then Rows(i).Font.Bold = True
Next i
End Sub

 

[shdawson]

OK, thanks.

 

[shdawson]

Hi,


Question about enhancing VBA, please.

Vog was kind enough to edit his code...

Sub BoldSbttls()
Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    If Range("A" & i).Value Like "* Total" Then
        Rows(i).Font.Bold = True
        Rows(i).Interior.ColorIndex = 15
    End If
Next i
End Sub

...to color the rows. Now, some of the columns have conditional formatting to make the font white, which is the same as the background, if the cell results with a 0 (zero). The VBA code makes for a nice gray background with a white 0.

How does one overcome conditional formatting with VBA, please?


Thanks,
S

 

[VoG]

Try

Sub BoldSbttls()
Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    If Range("A" & i).Value Like "* Total" Then
        Rows(i).Font.Bold = True
        Rows(i).Interior.ColorIndex = 15
        Rows(i).FormatConditions.Delete
    End If
Next i
End Sub

 

[shdawson]

Works great!


Thank you, kind sir. Have a great day


Respectfully,
SHD

 

[shdawson]

Hi,


Not sure what changed, but this code now over formats...so to speak.

Sub BoldingSubtotaledRows()
'ADJUST THE ***RANGE*** TO BE WHATEVER IT NEEDS TO BE...
'A is "Tower"...
Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    If Range("A" & i).Value Like "* Total" Then
        Rows(i).Font.Bold = True
        Rows(i).Interior.ColorIndex = 15
        Rows(i).FormatConditions.Delete
    End If
Next i
End Sub

The code now formats all cells in the said row to gray, where it only did the rows that were accounted for in the subtotaling orginally.

What changed, please?


Thanks,
S

 

[shdawson]

Anything?


S