VBA highlight subtotal and grand total on multiple sheets

[galmond1010]

I would like to highlight column C with the text "Total" and "Grand Total" on multiple sheets using VBA. Can anyone assist? I have the VBA for a single sheet but can't seem to figure out how to go to the next sheet without debugging. Thank you in advance.

 

[Joe4]

Welcome to the Board!

Post the code you have, and we can help you expand it.
Also, do you want this to apply to ALL the sheets in the workbook, or just certain ones?
If just certain ones, how can we identify which ones?

 

[galmond1010]

Welcome to the Board!

Post the code you have, and we can help you expand it.
Also, do you want this to apply to ALL the sheets in the workbook, or just certain ones?
If just certain ones, how can we identify which ones?

Here is the code I current am using:
Dim rCell As Range
For Each rCell In Sheets("BURNS & ASSOC.").Range("C6:C10000")

If Right (rCell.Value,5) = "Total" Then
Rows(rCell.Row).Interior.ColorIndex = 36
End If
If Right(Rng.Value,11) = "Grand Total" Then
Rng.EntireRow.Interior.ColorIndex = 8
End If
Next

I have 31 individual sheets I would like to highlight subtotals in the same workbook.

 

[Joe4]

Try this:

Sub MyMacro()

    Dim ws As Worksheet
    Dim rCell As Range
    
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets
        ws.Activate
        For Each rCell In Sheets("BURNS & ASSOC.").Range("C6:C10000")
            If Right(rCell.Value, 5) = "Total" Then
                Rows(rCell.Row).Interior.ColorIndex = 36
            End If
            If Right(Rng.Value, 11) = "Grand Total" Then
                Rng.EntireRow.Interior.ColorIndex = 8
            End If
        Next
    Next ws

    Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

In this line you have the Rng object, but it does not exists:

[B][COLOR=#ff0000]Rng[/COLOR][/B][COLOR=#333333].EntireRow.Interior.ColorIndex = 8[/COLOR]

Try this:

Sub highlight_total()
    Dim sh As Worksheet, rCell As Range
    For Each sh In Sheets
        For Each rCell In sh.Range("C6", sh.Range("C" & Rows.Count).End(xlUp))
            If Right(rCell.Value, 5) = "Total" Then rCell.EntireRow.Interior.ColorIndex = 36
            If Right(rCell.Value, 11) = "Grand Total" Then rCell.EntireRow.Interior.ColorIndex = 8
        Next
    Next
End Sub

 

[Joe4]

In this line you have the Rng object, but it does not exists:

Good catch. I missed that.

 

[DanteAmor]

By the way, you did not change the sheet either

[COLOR=#333333]For Each rCell In [/COLOR][COLOR=#ff0000]Sheets("BURNS & ASSOC.")[/COLOR][COLOR=#333333].Range("C6:C10000")[/COLOR]

 

[Joe4]

By the way, you did not change the sheet either

Yeah, I meant to remove that part, since I am selecting the worksheet prior, i.e.

        ws.Activate
        For Each rCell In Range("C6:C10000")

Its been a long day, maybe I should quit now!

 

[DanteAmor]

Do not worry, it happens to all of us

 

[galmond1010]

Works like a charm. Thank you very much. Sorry about the original code, it was from an earlier attempt to highlight. I appreciate the help.