VBA help - highlight "total" rows.

Posted by David Morris on January 07, 2002 8:24 AM

I get daily reports that list hundreds of items in several different groups. The database exports into Excel with a "total" line at the top of each section.

Is there a VBA that will search for all cells that have "total" in a certain column and then highlight a certain range of cells in that row? What I want is to highlight the totals rows in green.

Sorry for the strange wording. I am completely new to the whole VBA deal. Not sure if I am explaining it right.

Thanks,
David

Posted by Tom Dickinson on January 07, 2002 9:04 AM

Light or Dark green? What is the last column? (NT)

Posted by David on January 07, 2002 9:32 AM

Re: Light or Dark green? What is the last column? (NT)

Tom,
Sorry, I was speaking of filling, not font color.
I usually fill with the Neon Green. The column & row numbers change from week to week depending on what sold. Can I just put a far out reference like A500 - as long as I make sure it is beyond the possible range?

David

Posted by Tom Dickinson on January 07, 2002 11:26 AM

Re: Light or Dark green? What is the last column? (NT)

Try this:

Sub GrnHghlt()
Dim Cnt As Integer
For Cnt = 1 To 500
If UCase(Range("A" & Cnt)) = "TOTAL" Then
Range("A" & Cnt, "B" & Cnt).Interior.ColorIndex = 4
End If
Next
End Sub

If the color is not right, (this is "bright green" on my spreadsheet, try 43 instead of 4 for "lime green", or 35 for "light green". The line "For cnt..." will go through rows 1 through 500. you can change it to start and stop where you want. The line "If UCase(..." sets the comparison for column "A". If the word totals is in another column, change accordingly. (The "UCase" part makes the word all caps for comparison purposes. It will not change how the word appears on the spreadsheet.) The line with "colorindex" will highlight columns A and B in the row. Change the letters to highlight what you need.

If there is more than 1 row that will have the word "total" that you want to find, let me know and I'll add an "offset" to the macro.

Hope this helps.