Forum Discussion
Percentage of 2 colors from a column to a cell
Good night, I'm making a spreadsheet in excel and I'm having trouble solving a problem. In column D2:D46 I have 2 cell colors (green and red) and I need cell D57 to show me how many % are green and red in column D2:D46. I just need her to see which of the two colors has the higher % and show me the result in her color. Example: in 10 cells I have 7 green and 3 red, I need it to show me in green, 70%... and as every day this information will change, so when in the column there are 7 red and 3 green, it shows me 70% in red! I need urgent help, I hope someone can help me! thanks
help_please360 You'll need some VBA code for this. Not my favorite subject but something I did a long time ago. This peace of code creates a user defined function that let's you count the number of cells with the same color as the cell where the formula is entered. From there you can divide the outcome by the total number of cells and format as a percentage.
Function CountColor(rngCol As Range) Application.Volatile Dim C As Long Dim Total As Variant C = Application.ThisCell.Interior.Color Total = 0 For Each Cell In rngCol If Cell.Interior.Color = C Then Total = Total + 1 End If Next CountColor = Total End FunctionI've attached a file with a working example of what you described.
Riny_van_Eekelen thanks for the reply my friend. It would be almost that! I need cell D57 to show me only the % of the most repeated color in column D2:D46. I will send you a picture for you to see! Hope you can help me, thanks!
help_please360 You'll need the two cells that calculate the % for each color, but you can hide them somewhere. D57 will then contain a formula that takes the maximum of the two. Use conditional formatting to color D57 reg or green. See attached.
