Count Color & Text Combined Cells

[GomaPile]

Hi All,

After days of researching through so many forums trying to find some web assistance for counting combined color cells with text, sadly, none of them helped me.

This Function vba code below works fine, but I like to count cells that contains the same color & any form of text together; looking at a range of cells. e.g. If, 5 cells contains data within a range of color cells "Yellow" for instance; it will return the value 5. But if one of the 5 yellow cells doesn't contain text than don't count me.

Can anyone please point me in the right direction that does both criteria in a single cell.

 Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    
    'CountCellsByColor(range, color code)
    'example =CountCellsByColor(C4:P4,B4)
    
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByColor = cntRes
End Function

Cell B4 has the color background:

=IF($A$4<1,"",CountCellsByColor(C4:P4,$B$4))

Cell A4 if the value = nothing (cool way to switch the formula On/Off)

Regards
Goma (NASA)

 

[spiralrain]

If I understand what your asking correctly: that this code already works for counting cells in a range that contain a certain color, but instead needs to count cells in a range that contain a certain color and contain any text.

Then I think all you should have to do is change this line:

If indRefColor = cellCurrent.Interior.Color Then

to:

If indRefColor = cellCurrent.Interior.Color and cellCurrent <> "" Then

I hope this helps.

 

[GomaPile]

If I understand what your asking correctly: that this code already works for counting cells in a range that contain a certain color, but instead needs to count cells in a range that contain a certain color and contain any text.

Then I think all you should have to do is change this line:

If indRefColor = cellCurrent.Interior.Color Then

to:

If indRefColor = cellCurrent.Interior.Color and cellCurrent <> "" Then

I hope this helps.

[SOLVED]
OMG!! it worked. If you can see me now I'm jumping with happiness. Thank you so so so so much Spiralrain

Awesome with a capital A
Thanks heaps
Goma (NASA)

 

[GomaPile]

Hello World, and Mr Excel,

I want to share this info for those who might be interested. While browsing on the internet looking for a quick fix solution to speed up the function vba color count code (above), "why is this so slow"; to which I didn't realize what was causing the issue in the first place. Anyway, the short story to this problem error, try to avoid using any volatile formulas and delete the line code 'Application.Volatile' up top, don't use it, its crap, its fast without it.

That's all have a nice day
Goma (NASA)