You are here:

Excel/colored cells that are really not colored


hi Richard, pleased to meet you!
I see that your "bio" holds many inquiries concerning colored cells, so I feel that hopefully you can help me.

I've been given a set of rows and columns, say, 4 columns and 100 rows. The cells in this range of rows and columns have already been treated in some way. Some rows have no colored cells, some rows have all cells colored, and some rows have some cells colored and some cells not colored.
The color in the cells was created by using conditional formatting, whose criteria I don't know.
I do know that the color generated when using conditional formatting is a fake. By which I mean that if I highlight a cell with such color, then click on Format Painter, then click in a uncolored blank cell, the blank cell remains uncolored.
I believe that you and all experts are aware of this "weakness". Why Microsoft has not remedied this problem, I don't know.

Here is my query:
Say cell A4 is white, cells B4,C4 and D4 are fake-colored (by which I mean they appear colored, but are really not colored at all, as I explained above). I need a way of counting these 4 cells and entering the result, 3, in a nearby cell, 3 being the number of colored (again, fake color)cells.
In another row of 4 columns, cells A6, B6 and D6 are white, cell C6 is fake-colored. In a nearby cell, I need to enter the result, 1, in a nearby cell, 1 being the number of fake-color cells.

I  hope you can help me.
Your replies to others are encouraging.



When you copy a conditional format to a blank cell the chances are the values in the blank cell will not meet the conditional format criteria, therefore the blank cell will remain blank.  As an example, if the condition is that the cell be greater than zero, then the format will show up if the cell value is greater than zero.  If you copy that format to a blank cell then obviously the format criteria is not met because it is blank. But if you copy that same format to a cell with a 1 in it then the format criteria will have been met so the cell be formatted as required by the conditional format criteria.  This is the same whether you use format painter or the direct copy and paste format method.

You can determine what the conditional formatting criteria is/are by clicking on conditional formatting on your toolbar, then selecting manage rules from the dropdown list, then selecting show formatting rules for the worksheet.  What will be displayed is all of the conditional formatting rules in the worksheet, and which cells they apply to.

As to counting the cells it is easiest to count based on the criteria that are being used for the conditional formatting.  Assuming that your criteria is to color any cell that is greater than one.  The formula might be

This formula counts all of the cells in the range that are greater than 1 and gives you the total of the number of cells. Of course this happens to be the total number of cells that are formatted via the conditional formatting. (If you want the sum of the cell values where the cells are greater than zero then you would need the SUMIF() formula) You might also investigate the other COUNT formulas such as COUNT, COUNTA, COUNTBLANK, COUNTIFS.  These might be more appropriate to your situation and useful in your formulas.

Hope this works for you.

Florida (81 and sunny right now)
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


Richard Roberts


Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.


Have been working with Excel for about 20 years primarily in accounting and financial areas.

BA, Certified Public Accountant

©2017 All rights reserved.