Count merged cells as if unmerged
 

I had this neat macro that counts how many cells are a certain color:



Public Function CountColors(color As String) As Integer

Dim intCount As Integer

Dim cell As Range



For Each cell In Range("ThisRange").Cells

If cell.Interior.color = 825735 Then intCount = intCount + 1

Next cell

CountColors = intCount

End Function



The problem is, I have merged some cells. How can I get a macro to count
say, 3 cells merged into 1 as three cells, not 1, if they match a certain
color?.

Count merged cells as if unmerged
 

Try this:

Public Function CountColors(ColorNbr As String) As Long
Dim lCount As Long, c As Range
lCount = 0
For Each c In Range("ThisRange")
If c.Interior.color = ColorNbr Then
If c.MergeCells = True Then
lCount = lCount + c.Count
Else
lCount = lCount + 1
End If
End If
Next c
CountColors = lCount
End Function

Sub TEST()
MsgBox CountColors("825735")
End Sub

Hope this helps,

Hutch

"John" wrote:

I had this neat macro that counts how many cells are a certain color:



Public Function CountColors(color As String) As Integer

Dim intCount As Integer

Dim cell As Range



For Each cell In Range("ThisRange").Cells

If cell.Interior.color = 825735 Then intCount = intCount + 1

Next cell

CountColors = intCount

End Function



The problem is, I have merged some cells. How can I get a macro to count
say, 3 cells merged into 1 as three cells, not 1, if they match a certain
color?.

Count merged cells as if unmerged
 

Count the number of merged cells:

If cell.Interior.color = 825735 Then intCount = intCount +
cell.MergeArea.Cells.Count

cell.MergeArea.Cells.Count will return 1 for cells that are not merged....

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
I had this neat macro that counts how many cells are a certain color:



Public Function CountColors(color As String) As Integer

Dim intCount As Integer

Dim cell As Range



For Each cell In Range("ThisRange").Cells

If cell.Interior.color = 825735 Then intCount = intCount + 1

Next cell

CountColors = intCount

End Function



The problem is, I have merged some cells. How can I get a macro to count
say, 3 cells merged into 1 as three cells, not 1, if they match a certain
color?.

Count merged cells as if unmerged
 

Hi Bernie,

The OP's question is highly ambiguous, but wouldn't adding
cell.MergeArea.Cells.Count result in an overly excessive count, where
multiple cells in a MergeArea are looped.

(My guess is the intention of the question is the opposite of what it says,
only count the first cell in the mergarea that matches the condition, but I
may well be wrong!)

Regards,
Peter T

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Count the number of merged cells:

If cell.Interior.color = 825735 Then intCount = intCount +
cell.MergeArea.Cells.Count

cell.MergeArea.Cells.Count will return 1 for cells that are not merged....

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
I had this neat macro that counts how many cells are a certain color:



Public Function CountColors(color As String) As Integer

Dim intCount As Integer

Dim cell As Range



For Each cell In Range("ThisRange").Cells

If cell.Interior.color = 825735 Then intCount = intCount + 1

Next cell

CountColors = intCount

End Function



The problem is, I have merged some cells. How can I get a macro to count
say, 3 cells merged into 1 as three cells, not 1, if they match a certain
color?.

Count merged cells as if unmerged
 

Peter,

Only the OP knows for sure, but the question was:

How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1

Which will give the wrong result if the selection covers the merged cell, but if the OP is stepping
down a column, for example, then it would work.

HTH,
Bernie
MS Excel MVP


"Peter T" <peter_t@discussions wrote in message ...
Hi Bernie,

The OP's question is highly ambiguous, but wouldn't adding cell.MergeArea.Cells.Count result in an
overly excessive count, where multiple cells in a MergeArea are looped.

(My guess is the intention of the question is the opposite of what it says, only count the first
cell in the mergarea that matches the condition, but I may well be wrong!)

Regards,
Peter T

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Count the number of merged cells:

If cell.Interior.color = 825735 Then intCount = intCount + cell.MergeArea.Cells.Count

cell.MergeArea.Cells.Count will return 1 for cells that are not merged....

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
I had this neat macro that counts how many cells are a certain color:



Public Function CountColors(color As String) As Integer

Dim intCount As Integer

Dim cell As Range



For Each cell In Range("ThisRange").Cells

If cell.Interior.color = 825735 Then intCount = intCount + 1

Next cell

CountColors = intCount

End Function



The problem is, I have merged some cells. How can I get a macro to count say, 3 cells merged
into 1 as three cells, not 1, if they match a certain color?.

Count merged cells as if unmerged
 

Indeed that's one (likely) interpretation of the question I didn't think
of.,
- and cells merged vertically in that column, oh I give up <g

Regards,
Peter T

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

Only the OP knows for sure, but the question was:

How can I get a macro to count say, 3 cells merged into 1 as three cells,
not 1

Which will give the wrong result if the selection covers the merged cell,
but if the OP is stepping down a column, for example, then it would work.

HTH,
Bernie
MS Excel MVP


"Peter T" <peter_t@discussions wrote in message
...
Hi Bernie,

The OP's question is highly ambiguous, but wouldn't adding
cell.MergeArea.Cells.Count result in an overly excessive count, where
multiple cells in a MergeArea are looped.

(My guess is the intention of the question is the opposite of what it
says, only count the first cell in the mergarea that matches the
condition, but I may well be wrong!)

Regards,
Peter T

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Count the number of merged cells:

If cell.Interior.color = 825735 Then intCount = intCount +
cell.MergeArea.Cells.Count

cell.MergeArea.Cells.Count will return 1 for cells that are not
merged....

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
I had this neat macro that counts how many cells are a certain color:



Public Function CountColors(color As String) As Integer

Dim intCount As Integer

Dim cell As Range



For Each cell In Range("ThisRange").Cells

If cell.Interior.color = 825735 Then intCount = intCount + 1

Next cell

CountColors = intCount

End Function



The problem is, I have merged some cells. How can I get a macro to
count say, 3 cells merged into 1 as three cells, not 1, if they match a
certain color?.

Count merged cells as if unmerged
 

I appreciate all the help. I decided to unmerge my cells, since I couldn't
take the risk of the cell count being inaccurate.

"Peter T" <peter_t@discussions wrote in message
...
Indeed that's one (likely) interpretation of the question I didn't think
of.,
- and cells merged vertically in that column, oh I give up <g

Regards,
Peter T

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

Only the OP knows for sure, but the question was:

How can I get a macro to count say, 3 cells merged into 1 as three
cells,
not 1

Which will give the wrong result if the selection covers the merged cell,
but if the OP is stepping down a column, for example, then it would work.

HTH,
Bernie
MS Excel MVP


"Peter T" <peter_t@discussions wrote in message
...
Hi Bernie,

The OP's question is highly ambiguous, but wouldn't adding
cell.MergeArea.Cells.Count result in an overly excessive count, where
multiple cells in a MergeArea are looped.

(My guess is the intention of the question is the opposite of what it
says, only count the first cell in the mergarea that matches the
condition, but I may well be wrong!)

Regards,
Peter T

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Count the number of merged cells:

If cell.Interior.color = 825735 Then intCount = intCount +
cell.MergeArea.Cells.Count

cell.MergeArea.Cells.Count will return 1 for cells that are not
merged....

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
I had this neat macro that counts how many cells are a certain color:



Public Function CountColors(color As String) As Integer

Dim intCount As Integer

Dim cell As Range



For Each cell In Range("ThisRange").Cells

If cell.Interior.color = 825735 Then intCount = intCount + 1

Next cell

CountColors = intCount

End Function



The problem is, I have merged some cells. How can I get a macro to
count say, 3 cells merged into 1 as three cells, not 1, if they match
a
certain color?.