How to count background color

[mangesh]

Hi,
Is there a way to count the background color from a range of cell?
for example: In a range of A1:B5 there are 5 cells highlighted with yellow background, the formula should give me 5 counts for yellow background.

[Olly]

A simple UDF can count all cells with a colored background:

Please Login or Register  to view this content.

Use as:

Please Login or Register  to view this content.

[Olly]

Extending that a little allows us to count cells with a specific color fill:

Please Login or Register  to view this content.

Use as:

Please Login or Register  to view this content.

[mangesh]

Hi,
I have used the above macro provided by Olly as well as the below code
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
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

However, whenever there is a change in background color it gives me #NAME? error.
Does anyone know the possible reason?

[Olly]

It's not clear which function you have used, or how, so hard to advise why you have the #NAME error. Can you attach your workbook? If not, can you clearly explain which funtion you are using, and where.

[mangesh]

Its in built function generated by the macro code pasted above and function name is =ColorFunction.

[Olly]

Not specific enough to help you. This doesn't tell me where you have tried to use this function and which arguments you are passing to it.

Please attach a workbook.

Did you try using the function and formula I provided in Post #2?

[mangesh]

yes, I did, but same error is popping up the moment i close and open the file #NAME? error.

[Olly]

Please attach your workbook.

[mangesh]

Sorry, forgot to attach. Please find the attached. The moment i close and open the file it is giving me the error.

[Olly]

Works perfectly for me. Have you made sure that macros are enabled for this file? Then try pressing F9 to calculate the worksheet.

[mangesh]

Yes, It is working fine. Sorry to bother you i had not selected enables macro for this file. Thank you so much for your help.