Excel macro to count cells by color and does not count merged cells as more than 1

Question

How can I write a macro that will go through a range of cells and count the contents by color without it counting each individual cell within a group of merged cells.

For instance, in the picture, I want a macro to return a count of 2 yellow and 3 green but the macro I wrote returns a count of 8 yellow as it is counting the 7 merged yellow cells as individuals.....

Answer 1

Ugh. There are two bad practices in this sample that you should avoid: color coding cells to transport meaning, and merging cells. Don't. Just don't. Use a different coloumn to determine a status or category value, then you can use conditional formatting to set a cell color based on that status or category value. And you can use simple worksheet formulas to perform a count per status or category.

Merging cells causes more headaches than there is aspirin. Just don't merge cells. They cause issues with selection and, as you have noticed, with logic in VBA. Don't merge, and do a simple count with worksheet formulas. The "need" to merge cells can always be overcome with proper data architecture and worksheet structure.

Answer 2

I fully agree with @teylyn's warning, but if there are some very justified reasons for such a solution, you can use such a function, for example.

Function ColorCount(rng As Range, pat) As Long
    Dim cell As Range, cnt As Long
    Select Case TypeName(pat)
        Case "Range": pat = pat.Interior.Color
        Case "String": pat = Range(pat).Interior.Color
    End Select
    For Each cell In rng
        If cell.Address = cell.MergeArea(1).Address And _
            cell.Interior.Color = pat Then
            cnt = cnt + 1
        End If
    Next cell
    ColorCount = cnt
End Function

The function takes two arguments: the first is the range of cells to check, the second is the color pattern (this can be a reference, address text, or a numeric color value). The color of the cell from base formatting, not conditional.