# [SOLVED] How can you Automatically Highlight Excel Subtotal Rows



## beetle3247 (Jan 8, 2008)

I create an Excel worksheet from data that is extracted via a query from our mainframe files. I have written a set of macros to do all the repetitive formatting, including creating subtotals on the change of a column's value. Management would now like those subtotals to be highlighted so that they stand out from the detail lines (which they still want to see). I'm at a loss as to how to automcatically (through a macro?) identify which rows are the subtotals and highlight the entire row. I know I can use conditional formating to find and highlight the cell that has the word "Total" in it, but how can I highlight all the "Total" rows without having to do it manually? Any help would be appreciated.


----------



## AlbertMC2 (Jul 15, 2010)

*Re: How can you Automatically Highlight Excel Subtotal Rows*

Hi

So if the row has the word "Total" in it then you want to highlight that entire row?
Then try this macro:

```
Sub Find_Total()
    
    Dim lCount As Long
    Dim rFoundCell As Range
    
    On Error Resume Next
    
    Set rFoundCell = Range("A1")
    
    For lCount = 1 To WorksheetFunction.CountIf(Range("A:Z"), "Total")
        Set rFoundCell = Range("A:Z").Find(What:="Total", After:=rFoundCell, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
        rFoundCell.EntireRow.Interior.ColorIndex = 4
    Next lCount

End Sub
```
You may want to change the range to search then change both *Range("A:Z")*


----------



## beetle3247 (Jan 8, 2008)

*Re: How can you Automatically Highlight Excel Subtotal Rows*

My file has a range of A1:T145, so I changed the ranges to that and stepped into the macro. It gets to the "lCount" command and then immediately goes to the "End Sub". The "Total" word is in column B. What needs to be changed to make it work? I don't write macros, merely record steps and save as a macro, so I am a little lost here. 

Thanks for your help.


----------



## beetle3247 (Jan 8, 2008)

*Re: How can you Automatically Highlight Excel Subtotal Rows*

I see a problem. The "total" rows in an Excel file that are created using the "Subtotal" function have the value of the cell that the subtotal is based on with the word "Total" added at the end. So, my first "total" row is "A. Trade Other Total". If I change the "CountIf" function in the macro to look for "A. Trade Other Total" then it works just fine and highlights the entire row for that item. BUT, I need to find all occurences that have the word "Total" anywhere in the cell. What changes have to be made to the macro for that?


----------



## RSpecianjr (Jan 20, 2010)

*Re: How can you Automatically Highlight Excel Subtotal Rows*

Hey beetle3247,

Okay, so you don't have cells that contain just the word Total. You have cells containing other words with the word total. That definitely changes things haha. We need to know what kind of cell values we are looking for? Is there any consistency to it?

Alternately, since your "Total" cell is in column B, you could use another worksheet formula to deliver an identifier. What is the first empty column in your worksheet?

Regards,

Robert D. Specian Jr.


----------



## beetle3247 (Jan 8, 2008)

*Re: How can you Automatically Highlight Excel Subtotal Rows*

I found the solution. 

I was able to analyze the macro and determined that I would not need the "WorksheetFunction.CountIf" command to make it work. The function in the "Set rFoundCell" looks for the word "Total" by using the "Find" function. I changed the "lCount" to be a numeric range and it then worked just fine and found all the rows and highlighted them.


----------



## AlbertMC2 (Jul 15, 2010)

*Re: How can you Automatically Highlight Excel Subtotal Rows*

Alternatively you could have used *"*Total*"*
Which would've found all cells containing the word "*Total*" somewhere in it.

The countif just makes the macro faster and therefore more efficient.


----------



## RSpecianjr (Jan 20, 2010)

haha GOOD TO KNOW! I didn't think about those = P


----------

