You are here:

Excel/macros give incorrect answers


hello, Jerry!

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 problem:
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:
    =colorfunction(A1,A1:F3, FALSE)
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?  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques:


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: ===================== I have been offering free assistance as an Excel aid on many web sites for many years: ( - JBeaucaire) ======== ( - JBeaucaire) ======= ( - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2017 All rights reserved.