CODEWITHSUNDEEP
excelvba
Bekka
Bekka

Reputation: 23

In Excel, need to count cells that are colored from conditional formatting, then create report of specific results

I have a spreadsheet with names (in column A), and each name has 10 numbers in a row (Columns B-K). I am using Conditional Formatting to highlight cells in green that contain numbers which match other criteria.

Now, I need to count the green highlighted cells for each name, and create a report of the results. For example, I need a report, or list, or graph that shows all the names of the people that have 8 of the 10 cells highlighted in green. (Names with 8 green cells = Joe, Mike, Sue)

I am using the following formula just to count each row of green colored cells, but the list will have too many names to repeat this formula for each row. So I have not created a report based on the results of this formula, because I need a better formula to do the initial green cell count. Then, I need help on the best way to create the final report. Thank you!

enter image description here

Public Sub CountColorCells()
    'Variable declaration
    Dim rng As Range
    Dim lColorCounter As Long
    Dim rngCell As Range
    'Set the range
    Set rng = Sheet1.Range("B2:K2")
    'loop throught each cell in the range
    For Each rngCell In rng
        'Checking Green color
        If Cells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = _
                                                       RGB(169, 208, 142) Then
            lColorCounter = lColorCounter + 1
        End If
    Next
    'Display the value in cell L2
    Sheet1.Range("L2") = lColorCounter
 End Sub

Upvotes: 2

Views: 4291

Answers (1)

PeterT
PeterT

Reputation: 8557

I believe you really need a UDF here (user defined function). With a UDF, you can enter a formula in that would look like this

in cell L2: =CountColorCells(B2:K2) and that UDF would return the number of highlighted cells in the range.

So there are some changes to make to your code.

First, you want to declare it as a Function, not a Sub since we need to return a value.

Next, your code is close to being correct as it is. The only problem is that when you're iterating over the range (which will be changed to an input parameter of the function), you don't need to break out the Row and Column. That's already baked in to the rngCell. So your UDF now looks like this (and works very fast)

Public Function CountColorCells(ByRef thisRange As Range) As Long
    Dim lColorCounter As Long
    Dim rngCell As Range
    For Each rngCell In thisRange
        If rngCell.Interior.Color = RGB(169, 208, 142) Then
            lColorCounter = lColorCounter + 1
        End If
    Next
    CountColorCells = lColorCounter
End Function

And now (only because I can't help it ;) ), here is a version of the UDF that I would use. There are now some optional parameters for Red, Green, and Blue color values you can add, just in case you want to count a different color. So with this UDF version, you can count cells that are RED by using the formula =CountColorCells(B2:K2,255,0,0). This is just an extended example of what you can do:

Public Function CountColorCells(ByRef thisRange As Range, _
                                Optional ByVal r As Long = 169, _
                                Optional ByVal g As Long = 208, _
                                Optional ByVal b As Long = 142) As Long
    Dim checkColor As Long
    checkColor = RGB(r, g, b)

    Dim lColorCounter As Long
    Dim rngCell As Range
    For Each rngCell In thisRange
        If rngCell.Interior.Color = checkColor Then
            lColorCounter = lColorCounter + 1
        End If
    Next
    CountColorCells = lColorCounter
End Function

Upvotes: 0

Related Questions