Counting colour of cells when some are merged

openedonce

New Member
Joined
Apr 28, 2014
Messages
5
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Try using the cell format "Center Across Selection" instead of Merge.

Format > Alignment > Horizontal > Center Across Selection > OK

May help.

Howard
 

mailpaga

New Member
Joined
Apr 23, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top