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.

 

[MickG]

Try :-

[COLOR="Navy"]Sub[/COLOR] MG21Jul22
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.MergeCells [COLOR="Navy"]Then[/COLOR]
        Dn.EntireRow.Interior.ColorIndex = 35
    [COLOR="Navy"]ElseIf[/COLOR] Not Dn.MergeCells [COLOR="Navy"]Then[/COLOR]
        Dn.EntireRow.Interior.ColorIndex = 43
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick