I have a table with the subtotals created based on the excel menu (data->subtotal). Now I'd like to paint the cells gray and make the lines that say subtotal bold. Is it possible with vba code? Column A displays several cells with the following form: total code " KKK Total".

Thanks

CodePudding user response：

Color Rows of a Range

• It is assumed that the data 'nicely' starts in cell A1 and has one row of headers.

Option Explicit

Sub ColorLines()
    
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    If ws.FilterMode Then ws.ShowAllData
    
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    Dim drg As Range: Set drg = rg.Resize(rg.Rows.Count - 1).Offset(1)
    
    rg.AutoFilter 1, "*Total" ' ends with or e.g. "*Total*" contains
    
    Dim vrg As Range
    On Error Resume Next
        Set vrg = drg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    ws.AutoFilterMode = False
    
    If Not vrg Is Nothing Then vrg.Interior.Color = 13553360
    
    Application.ScreenUpdating = True
    
    MsgBox "Subtotals colored.", vbInformation
    
End Sub

CodePudding user response：

What cells do you want to paint grey? Everything? Then it's easier to just select everything (cntrl a) and use the fill cell. You can also record this with the Macro recorder.

If you want to make a cell which contains "subtotal" bold, then use this:

Sub bold()

Dim Rng As Range
Application.ReplaceFormat.Font.FontStyle = "Bold"
With ThisWorkbook.Sheets("Sheet1")
   Set Rng = .Range("A:A")
   Rng.Cells.Replace What:="Subtotal", Lookat:=xlWhole, Replacement:="Subtotal", 
SearchFormat:=False, ReplaceFormat:=True
End With

End Sub