Counting colour of cells when some are merged

[openedonce]

Hi,

I have a VBA function in a module that counts cell colour:

Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function

I then colour fill cell A4 with the colour I wish to count and I have in A5 the following formula

=ColorCount(J$3:X$50,A4)

My function works except for merged cells, as it does not treat it as a singular entity, so 2 cells that are merged together will be counted as 2, rather than 1.

Is there a simple addition to the above function's code, which will treat merged cells as 1 colour count when it comes across them?

I realise that having merged cells on a worksheet has led to my problem but I need to use them to help the layout of the grid I have created, as a few cells have a lot more written information in than others.

Any help will be gratefully received.

Simon

 

[L. Howard]

Try using the cell format "Center Across Selection" instead of Merge.

Format > Alignment > Horizontal > Center Across Selection > OK

May help.

Howard

 

[mailpaga]

Function CountCcolor(range_data As Range, criteria As Range) As Long
' count color cells including merged ones
Dim datax As Range
Dim xcolor As Long
Dim a, b
xcolor = criteria.Interior.ColorIndex
CountCcolor = 0
For Each datax In range_data
a = Cells(datax.Row - 1, datax.Column).Interior.ColorIndex
b = Cells(datax.Row, datax.Column - 1).Interior.ColorIndex
If datax.Interior.ColorIndex = xcolor And a <> xcolor And b <> xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function