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.
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.
thank you
A simple UDF can count all cells with a colored background:
Use as:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Extending that a little allows us to count cells with a specific color fill:
Use as:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
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?
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.
Its in built function generated by the macro code pasted above and function name is =ColorFunction.
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?
yes, I did, but same error is popping up the moment i close and open the file #NAME? error.
Please attach your workbook.
Sorry, forgot to attach. Please find the attached. The moment i close and open the file it is giving me the error.
Works perfectly for me. Have you made sure that macros are enabled for this file? Then try pressing F9 to calculate the worksheet.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks