You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- macros give incorrect answers

Advertisement

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:

=CountCcolor(A1:F3,A1)

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

=CountCcolor(A1:F2,A1)

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)?

Thanks

Chris

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.

COUNTIF

COUNTIFS

So, why are some cells yellow?

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

Excel

Answers by Expert:

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files
=====================
I have been offering free assistance as an Excel aid on many web sites for many years:
(http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)**Education/Credentials**

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

Microsoft Excel MVP 2010