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

what countbycolour module?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"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

Everyone is supposed to know where you got the countbycolor module?

If you post the code, then perhaps someone can advise how to modify it.

--
Regards,
Tom Ogilvy

"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

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

The code I was working with is:

Function CountByColor(InputRange As Range, ColorRange As Range) As
Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0

For Each cl In InputRange.Cells

If cl.Interior.ColorIndex = ColorIndex Then
TempCount = TempCount + 1
End If
If cl.MergeCells = True Then
Next cl
End If

Next cl
Set cl = Nothing
CountByColor = TempCount
End Sub

This works well, but I need it count a merged cell as a single cell,
vice the number of cells merged.

Thanks.


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

but I need it count a merged cell as a single cell

Yes, and that is what my posted sub does.

RBS


dsamson wrote:
The code I was working with is:

Function CountByColor(InputRange As Range, ColorRange As Range) As
Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0

For Each cl In InputRange.Cells

If cl.Interior.ColorIndex = ColorIndex Then
TempCount = TempCount + 1
End If
If cl.MergeCells = True Then
Next cl
End If

Next cl
Set cl = Nothing
CountByColor = TempCount
End Sub

This works well, but I need it count a merged cell as a single cell,
vice the number of cells merged.

Thanks.


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

Keep in mind I have no idea what I'm doing. My original code was
something I found on the web. Do I replace my code with yours in its
entirety, or is it something I place within my original? In the
latter, my result is a "#NAME?"


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

You will have to explain what exactly you are trying to do.
We can probably forget about that module you are talking about as it doesn't
do what you want.
The code I posted is a Sub that will run by it-self, so no, don't place it
in whatever other code you have.
I don't think this is a complicated problem, but your explanation just isn't
clear at all.

RBS

"dsamson" wrote in
message ...

Keep in mind I have no idea what I'm doing. My original code was
something I found on the web. Do I replace my code with yours in its
entirety, or is it something I place within my original? In the
latter, my result is a "#NAME?"


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