highlighting alternating blocks of rows based on cell text

[OhioRuss]

I would find it very helpful to have a general method to highlight alternating blocks of lines based on the text in one column (column A for purposes of illustration-see attached example). The sheet would be sorted by that column so that all rows with the same text (eg text1) would be together. Each time the text in column A changes, I want the background color to switch as illustrated so the blocks are easy to see.

I imagine a little macro that examines each cell in column A and compares its text to the cell above. If the same, make the row fill the same as the row above, otherwise change the row fill to the alternative fill. I just don't know how to program that.

Thanks in advance!

Ohioruss

[Squeaky]

Hi Ohio, This solution does not use VBA but uses conditional formatting. Since you said the header1 text will be grouped together, I set up a formula to determine if the row above any given text has the same header, then assigning a number (in column K). Hide or move them as needed. Using conditional formatting you will need to assign a background color to each number. I set this up to repeat every 5 colors since I do not know how many text blocks you have, and every number-color needs to be set up individually.
Drag down the formulas as far as you need.

Good Luck,
Squeaky

[OhioRuss]

Thanks Squeaky. I was able to get the column of numbers using your IF statement (1 and 2 were enough for me), which I then converted into actual numbers. I then temporarily sorted the spreadsheet by these numbers so the ones were together and the twos were together. I then manually colored them before putting the spreadsheet back to the original sequence. I found the conditional formatting unsatisfactory because it slowed down scrolling way too much (my sheet has 370,000 rows).

Ohioruss

[Kaper]

As for macro - try this one:

Please Login or Register  to view this content.

BTW. Please update your excel version in your profile info. For sure you are not processing 370 000 rows in Excel 2003 (neither in 97 )

[OhioRuss]

Thanks Kaper. I will keep this in mind for the next time and maybe it will be useful to others as similar questions have come up before.

I updated my profile as you suggested.

Ohioruss