If I understand right then this will do what you want:

Sub test()

Dim rng As Range
Dim rngMergeCells As Range
Dim collMergeRanges As Collection
Dim n As Long
Dim i As Long
Dim collCount
Dim bInMerge As Boolean
Dim btColorIndex As Byte

btColorIndex = 3

Set collMergeRanges = New Collection

For Each rng In Range(Cells(1), Cells(10, 10)).Cells
If rng.MergeCells Then
collCount = collMergeRanges.Count
If collCount 0 Then
bInMerge = False
For i = 1 To collCount
If Not Intersect(collMergeRanges(i), rng) Is Nothing Then
'in merged area, so has been dealt with already
bInMerge = True
Exit For
End If
Next
End If
If bInMerge = False Then
'part of merged cells, but not been dealth with yet
If rng.Interior.ColorIndex = btColorIndex Then
n = n + 1
End If
collMergeRanges.Add rng.MergeArea
End If
Else
'not part of merged cells
If rng.Interior.ColorIndex = btColorIndex Then
n = n + 1
End If
End If
Next

MsgBox n, , _
"number of single cells or merged ranges with colorindex " &
btColorIndex

End Sub


RBS


"dsamson" wrote in
message ...

I have multiple merged cells, each with any of 3 possible colors. When
I use the countbycolor module, it counts all the cells in the merge
area. What I want is to count the merge areas, not the individual
cells, by color. Any hints?


--
dsamson
------------------------------------------------------------------------
dsamson's Profile:
http://www.excelforum.com/member.php...o&userid=35528
View this thread: http://www.excelforum.com/showthread...hreadid=552955