Highlighting alternating clusters of rows

[CatalysTim]

Hi,

The picture below should illustrate what I'm trying to do. All the tutorials I've looked at so far are limited to conditional formatting for strictly alternating rows, whereas the database I'm building often has clusters of rows as defined by merged cells in the first and second columns.

Any advice would be greatly appreciated. Thanks.

Screen Shot 2015-07-21 at 10.42.20 AM.png

[Kaper]

Use conditional formatting based on formula. for instance on similar to such one:

Please Login or Register  to view this content.

this is for one color the other (when the condition is not met - format as standard fill of cells).

see attachment

[Kaper]

of course instead of MOD(...,2) you could use ISODD(...) or ISEVEN(...)

[FDibbins]

Kaper, that will work for 2 rows, but not 3?

[Kaper]

Hi Ford,
I think will work for any number 1,2,3 etc rows in a cluster.
Note that formula refers to column A, where for each cluster is only one merged cell.

PS. see the attachment :-)

[FDibbins]

My apologies, you are correct

[Kaper]

My pleasure,
and thanks for reputation

[CatalysTim]

Thanks for the quick help, Kaper. A couple of questions:

1. How would I modify the code to incorporate two alternating colors instead of (one color)+(no color)?
2. When I apply filters to the column with merged cells (or any of the subsequent columns, for the matter), the alternating color scheme effectively doesn't work because it doesn't take into account visible vs. hidden rows. Would it be possible to have the formatting account for this?