How to Count Cells based on Background Color

[MissMaggie]

---

Hello,
I am trying to figure out why the code won't work for counting colored cells. I think it should be that when I click in a blank cell and type =ColorFunction then I can select the cell with the background color I want to count, the range, and False (true should give the sum) Not sure what I am doing wrong as far as user error. Thank you!

-Maggie

---

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
End If
ColorFunction = vResult
End Function

---

 

[tonyyy]

Miss Maggie,

Don't think it's user error. The code has a .Sum function but doesn't include anything to "count." You might consider the following...

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

lCol = rColor.Interior.ColorIndex
For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
        If SUM = True Then
            vResult = WorksheetFunction.SUM(rCell, vResult)
        Else
            vResult = vResult + 1
        End If
    End If
Next rCell
ColorFunction = vResult
End Function

Cheers,

tonyyy

 

[MissMaggie]

Thank you!!! This worked.

 

[MissMaggie]

Thank you Tony. This works great. Do you think there is a way to do it for a cell that has a color gradient of two colors?

 

[tonyyy]

Thank you Tony. This works great. Do you think there is a way to do it for a cell that has a color gradient of two colors?

You're very welcome...

As for adapting the code to work with color gradients, I'm not aware of a method to test for the existence of a gradient in a cell, which would be required to execute the above macro. In my attempts, the revised code executes properly only if all the cells in the selection contain a gradient fill, otherwise it fails. Sorry.

 

[mrc44]

I tried this formula. both in excel 2016 and 2007. I keep getting #NAME ? error in both cases. Macros are enabled in excel. What am I doing wrong here? Any suggestions, please?

 

[Kenneth Hobson]

Did you put it into a Module?

Did you use the right input types for the formula?

Remember, this will not be a UDF that returns all color changes automatically.

 

[mrc44]

yes, I checked everything. but it still doesn't work.

 

[mrc44]

ok, this is strange. I created a new blank workbook, and tried the same formula there. and it works. but it doesn't work on my actual workbook.

 

[Kenneth Hobson]

Not sure what "doesn't work" means. Is it the Name error?

Keep in mind that it is looking at manually set interior index colors, not conditionally formatted interior colors.