Automatically color merged cells

[Rick]

Using Excel '07, I'm trying to automatically highlight a list of vertically
merged cells the same color as their unmerged rows next to them. For example:

Column A might have cells A2-A5 merged, while cells B2-B5 are not. I'd like
the entire row(s) all the same color, and the next merged set a different
color (A6-A7 merged, with B2-B7 not). Is this possible to do automatically?
Automatic table formatting will unmerge cells in column A and alternately
color each individual row. I would like the groups of rows to each be
colored differently.

Thanks

 

[Dave Peterson]

You mean like banding--red, yellow, red, yellow, red, yellow????

If yes, then you could use something like:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myColor As Long

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row

iRow = FirstRow
myColor = 3
Do
If iRow > LastRow Then
Exit Do
End If

.Cells(iRow, "A").MergeArea.EntireRow.Interior.ColorIndex = myColor

iRow = iRow + .Cells(iRow, "A").MergeArea.Rows.Count

If myColor = 3 Then
myColor = 6
Else
myColor = 3
End If
Loop
End With
End Sub

If you don't like those colors, record a macro when you change the fill to
colors that you do like and substitute them in the code (4 places!).