Forum Discussion
Counting Cells By Colour
Dear Christopher, I propose a small function to create in the vba module
Function color1(cellRef, Range)
Set reference = cellRef
numeroColor = reference.Interior.Color
Set myRange = Range
For Each sel In myRange.Cells
If sel.Interior.Color = numeroColor Then myColor = myColor + 1
Next
color1 = myColor
ActiveSheet.Calculate
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
Regards
Patrice
First off I am assuming the color is being done using conditional formatting and hence there is already some formula calculating right vs wrong so I would ask Christopher1209 why not just use that formula on the range to calculate % correct (if you share the formula / sheet we can help if needed). We might also have other suggestions to make it more efficient.
As for the VBA/macro (@Patrice Brel) I like how you can pass a reference cell to define the color but might suggest you declare and set myColor =0 before the loop. Also, I wonder if you could have used the volatile declaration instead of the forced ActiveSheet.Calculate as I think it would be more efficient if excel only was forced to update that function (and its dependents) than updating the whole sheet. And lastly for the benefit of the OP, I would mention this macro will return the # of cells of a particular color, so in the example you would want something like:
=color1($A$2,A5:A16) / (color1($A$2,A5:A16) + color1($B$2,A5:A16) )
to return the fraction (i.e. percent)
- Interesting. Thanks. I have problem with "volatile declaration" on my computer. The cells are not automaticaly updated. Regards Patrice
Patrice Brel I originally didn't test your macro, just looked at the code when I made those observations. NikolinoDE makes a very good point. Basically only looking at .interior.color doesn't work for conditional formatted colors because the cell's interior color is still blank (or what ever color you set it to) but the conditional formatting overrides that color formatting.
that said Nikolino's code is summing the VALUES in the cells of those colors instead of counting them. i also had issues with the file format and not recognizing the functions correctly so here is Nikolino's file and macro with slight corrections but credit goes to Niko.