Forum Discussion
Counting Cells By Colour
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.
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.
8 Replies
If it absolutely must be with VBA, here is an additional example.
Example in the inserted Excel file.
- Hello, I don't know the use of "@" in functions? is that a code or a single character?
- 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.
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 FunctionPrivate Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
End SubRegards
Patrice
- 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)- Interesting. Thanks. I have problem with "volatile declaration" on my computer. The cells are not automaticaly updated. Regards Patrice
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.
I know I don't know anything (Socrates)
