VBA - Format Rows containing word 'Total'
Results 1 to 9 of 9

Thread: VBA - Format Rows containing word 'Total'

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    267
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Format Rows containing word 'Total'

    Hi,
    I'm using the function Subtotal on my Defined Name range. I would like to format the totals that come through, so that they stand out from the rest of the data. I would like to do this without necessary referring to exact rows/cells, so that the format will work even if the data changes.
    My named range is 'CordisTest1'
    My data headings are always on row 52
    K52 is for heading 'Account' and is what the Subtotal function is based on.
    I would like it if column K in my named range 'CordisTest1' contains the word 'Total', cells A:J to the left of it will be formatted to have a plain line border around only, fill 15% grey and be bold font.
    Not sure if this is even possible. If not, please could you let me know. Thank you!

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,406
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA - Format Rows containing word 'Total'

    Try this:

    If the formatting piece isn't quite perfect (i.e. wrong color), you can turn on the Macro Recorder and record yourself setting the color you want. Then you can copy over the correct color number into the code.
    Code:
    Sub MyFormat()
    
        Dim lr As Long
        Dim r As Long
        
        Application.ScreenUpdating = False
        
    '   Find last row with data in column K
        lr = Cells(Rows.Count, "K").End(xlUp).Row
        
    '   Loop through all rows starting on row 53
        If lr < 53 Then Exit Sub
        For r = 53 To lr
    '       Check for existence of the word Total in the cell
            If Right(Cells(r, "K"), 5) = "Total" Then
    '           Apply your formatting
                With Range(Cells(r, "A"), Cells(r, "J"))
    '               Bolding
                    .Font.FontStyle = "Bold"
    '               Color
                    .Interior.ThemeColor = xlThemeColorDark1
                    .Interior.TintAndShade = -0.249946592608417
                End With
    '           Borders
                With Range(Cells(r, "A"), Cells(r, "J")).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With Range(Cells(r, "A"), Cells(r, "J")).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With Range(Cells(r, "A"), Cells(r, "J")).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With Range(Cells(r, "A"), Cells(r, "J")).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
            End If
        Next r
    
        Application.ScreenUpdating = True
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    267
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Format Rows containing word 'Total'

    Thank you so, so, so, so, so, so much! Perfect!

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,401
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: VBA - Format Rows containing word 'Total'

    I see that you have a solution that you are happy with but in case you are interested, here is another option that you might like to try that does not require as much looping or as much manipulation of border parts.

    Code:
    Sub FormatTotals()
      Dim rw As Range
      
      Application.ScreenUpdating = False
      With Range("A52", Range("K" & Rows.Count).End(xlUp))
        .AutoFilter Field:=11, Criteria1:="*Total"
        For Each rw In .Offset(1).Resize(.Rows.Count - 1, 10).SpecialCells(xlVisible).Rows
          With rw
            .Font.Bold = True
            .Interior.Color = 12566463
            .BorderAround xlContinuous
          End With
        Next rw
        .Parent.AutoFilterMode = False
      End With
      Application.ScreenUpdating = True
    End Sub
    Last edited by Peter_SSs; Aug 17th, 2019 at 02:38 AM. Reason: wrong code posted
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,401
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: VBA - Format Rows containing word 'Total'

    Quote Originally Posted by Peter_SSs View Post
    ... does not require as much looping ...
    Actually, no need to loop at all, we can do them all at once.

    Code:
    Sub FormatTotals_v2()
      Dim rw As Range
      
      Application.ScreenUpdating = False
      With Range("A52", Range("K" & Rows.Count).End(xlUp))
        .AutoFilter Field:=11, Criteria1:="*Total"
        With .Offset(1).Resize(.Rows.Count - 1, 10).SpecialCells(xlVisible).Rows
          .Font.Bold = True
          .Interior.Color = 12566463
          .BorderAround xlContinuous
          .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        End With
        .Parent.AutoFilterMode = False
      End With
      Application.ScreenUpdating = True
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    267
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Format Rows containing word 'Total'

    Quote Originally Posted by Peter_SSs View Post
    I see that you have a solution that you are happy with but in case you are interested, here is another option that you might like to try that does not require as much looping or as much manipulation of border parts.
    Thank you! You make it look so simple and short. Works a charm

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,401
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: VBA - Format Rows containing word 'Total'

    Quote Originally Posted by tlc53 View Post
    Thank you! You make it look so simple and short. Works a charm
    You have quoted my text from post 4 but hopefully you are referring to the code from post 5.
    In any case, you are very welcome!
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    267
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Format Rows containing word 'Total'

    Yes, I used the new code

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,401
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: VBA - Format Rows containing word 'Total'

    Quote Originally Posted by tlc53 View Post
    Yes, I used the new code
    Cheers.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •