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
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