Excel/macros give incorrect answers
I want to count the number of cells colored with various colors. I start off with a spreadsheet that has only the yellow color in some cells.
I've found two sites that offer macros for doing the counting. One uses the declaration CountCcolor, the other one uses colorfunction.
The filled cells are in A1 through F1, and A3 through F3. Row 2 has unfilled cells. The total of these three rows has (6 x 3=)18 cells. Only three cells are of yellow color, the others have the standard color. I enter the formula:
and the result is 18, but should only be 3.
I enter another formula:
and again the result is 18, but should only be 3.
When I reduce the range, eliminating the second (empty) row, the formulas is now
and the result of the formula is (6 x 2=)12, which is wrong again, because only a few of the cells are colored, not all twelve.
Obviously, something is wrong, but I can't find the source of the problem. Can you help if I show you the code(s)?
I can certainly try, post the code and I'll see if anything jumps out.
But the problem may not be the code, it may be your workbook.
1) Are there merged cells in that range?
2) Are these cells manually colored or with conditional formatting.
An even more important question comes to mind.... "Why are these cells yellow?".
One of my earliest Excel tutors admonished me to remember that "Color is not data". I've never forgotten that. Usually, if there is SOME reason why a person colors one cell yellow and another NOT yellow, that same reason can either be detected in the data already there or ADDED as a new column of data that is included. Once that is done, all of Excel normal built in functions come back into play and it's easy.
So, why are some cells yellow?