countbycolor counts all cells in a merged cell

  • Thread starter Thread starter dsamson
  • Start date Start date
D

dsamson

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?
 
what countbycolour module?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
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.
 
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
 
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.
 
but I need it count a merged cell as a single cell

Yes, and that is what my posted sub does.

RBS
 
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?"
 
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
 
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
 
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
 
Back
Top