countbycolor counts all cells in a merged cell
 

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

countbycolor counts all cells in a merged cell
 

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

countbycolor counts all cells in a merged cell
 

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

countbycolor counts all cells in a merged cell
 

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

countbycolor counts all cells in a merged cell
 

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

countbycolor counts all cells in a merged cell
 

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

countbycolor counts all cells in a merged cell
 

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

countbycolor counts all cells in a merged cell
 

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

countbycolor counts all cells in a merged cell
 

I have 2 seperate columns of text. the cells within the columns
represent test results and grades are indicated by the color of the
cell. Within each there are are colors red, yellow, green & white.
Some of these columns contain groups of merged cells. On a seperate
worksheet, I have a pie chart that will illustrate the counts for each
column seperately, 1 chart for each column.

the original module I used initially looked great until I noticed that
the merged cells were being counted by the number of cells merged,
versus being counted as a single entity cell.

I've attached the workbook I'm working on. The worksheet, "RTM",
columns L & M are the areas I've referred to.

I appreciate the patience and time you've given.

dps


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

countbycolor counts all cells in a merged cell
 

OK, so it sounds my posted code should work if you replace this bit:
Range(Cells(1), Cells(10, 10))
with the particular range you are interested in.

And this bit:
btColorIndex = 3
with the particular color you are interested in.

After having done that just run that Sub.

RBS


"dsamson" wrote in
message ...

I have 2 seperate columns of text. the cells within the columns
represent test results and grades are indicated by the color of the
cell. Within each there are are colors red, yellow, green & white.
Some of these columns contain groups of merged cells. On a seperate
worksheet, I have a pie chart that will illustrate the counts for each
column seperately, 1 chart for each column.

the original module I used initially looked great until I noticed that
the merged cells were being counted by the number of cells merged,
versus being counted as a single entity cell.

I've attached the workbook I'm working on. The worksheet, "RTM",
columns L & M are the areas I've referred to.

I appreciate the patience and time you've given.

dps


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