Counting Cells By Colour

[Christopher1209]

Hello

I have a column where if an answer is correct the cell is filled in green, if it is incorrect the cell is filled in red and until the answer is known it remains unfilled. Is there a formula I can use that will determine which percentage of filled cells are green? For example… If 7 selections have been made, 3 cells are filled green whilst 3 are filled red and 1 remains unfilled I would expect to see 50% displayed in the cell with formula. If the unfilled cell subsequently becomes green it would then change to 57%.

Thanks I’m advance for any assistance in advance.

[#2]

@Christopher1209 

Use conditional formatting to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.

Use conditional formatting to highlight information

With your permission, if I may further recommend, if you follow a few small rules, you'll get a quicker and more accurate answer, and those who want to help don't have to invest time guessing the problem.

What I recommend: Always inform about the Excel version, operating system, storage medium/hard drive, OneDrive, Sharepoint, etc.) you use.

If possible and if necessary, add a file (without sensitive data) and use this file to describe your project step by step or add photos with the appropriate description.

Don't forget that not every Excel user has a clue about every job and that what you see he can't see.

In this link you will find some more information about it:

Welcome to your Excel discussion space!

Thank you for your understanding and patience

Hope I was able to help you with this info.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@Christopher1209 

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

[#4]

@Christopher1209 

If it absolutely must be with VBA, here is an additional example.

Example in the inserted Excel file.

[#5]

I'll throw my 2 cents in...
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)

[#6]

Interesting. Thanks. I have problem with "volatile declaration" on my computer. The cells are not automaticaly updated. Regards Patrice

[#7]

Hello, I don't know the use of "@" in functions? is that a code or a single character?

[#8]

@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.

[#9]

i believe those '@' symbols were inserted by excel because I believe he was using RANDBETWEEN(), which is a newer function but saved in a .xls format, which is older.