You are here:

Excel/using conditional formatting to color some cells and not others

Advertisement


Question
QUESTION: hi Jerry, nice to meet you!

I use Excel 2010 and Windows 7. I have a column with many rows; the cells are all filled. The numbers in the cells range from 1 to 100, but not in any particular order. The numbers are generated randomly, then converted to actual numbers (rather than formulas), using Paste Values.

Clicking the Home tab>Styles>Conditional Formatting>Highlight Cells Rules>Cells Rules I wind up with a dialog box "Greater than". The expression in the box is  "Format cells that are GREATER THAN:" followed by a text box which is to be filled in with a formula, followed by a list of colors from which I can choose.

My question: I need a formula that will provide color to only those cells that contain numbers that are equal to or greater.

I may not be giving you a clear picture, so here is a more specific example. Here is the column with 10 rows:

10 in cell B1
40
68
23
89
35
75
56
71
85 in cell B10


I need a formula that will color the cells containing 30% of the highest numbers in the column, i.e.,75,85,89.

I look forward to your reply

ANSWER: You can use the PERCENTILE function to find the percentage threshold of any set of numbers.
This will give you the threshold of 71.012:

=PERCENTILE($B$1:$B$10, 0.667)

So you can now use that as the basis of a Conditional Formatting rule:

1) Highlight B1:B10
2) CF Rule - use a formula:

=B1>=PERCENTILE($B$1:$B$10, 0.667)

---------- FOLLOW-UP ----------

QUESTION: hello again, Jerry!

I've been doing some more work on this problem and have found a puzzling matter.
After successfully coloring the desired cells using the PERCENTILE function, I wanted to use the color (of the colored cells)to color some other (uncolored) cells. And I tried to do so by using the Format Painter item. IT DIDN'T WORK!e.g., say the cells were colored yellow (chosen in the Conditional Formatting>Greater Than dialog box); when I tried to color some other empty cells by clicking Format Painter in the colored cells and discharging the color in the empty cells, I got nothing!! the empty cells stayed uncolored!!
The only way I could color the empty, uncolored cells, was to use the Home tab>Fill Color item.
Do you see what I'm driving at? The color in the cells that became colored using the Percentile function appears to behave differently from the cells colored using the Fill Color item.
This behavior has consequences...trying to use a macro to count colored cells did not work if the cells were colored using the PERCENTILE function.

Chris

Happy Thanksgiving holiday

ANSWER: First rule to live by..."Color is not data".  You'll save yourself hours of headaches by not trying to count or sum data based on colors you applied.  Trust me.

The reason you can't count how many "yellow" cells there are in a range that is showing "yellow cells" via a conditional formatting formula is because those cells are NOT really yellow.  They are whatever color (if any) that was applied with the color fill box.  Conditional formatting is a "function" that results in a color displayed to the user, but that color isn't really there.  It cannot be detected by other functions.

But all is not lost, remember rule #1 above?  Color isn't data anyway.

The solution is to go back to the beginning of how those cells got their color, the formula used in the conditional formatting rule DOES analyze the data in a specific manner.  You need only use that same formula as the basis for creating a SUMIF or COUNTIF formula.

Something like:  =COUNTIF($B$1:$B$10, PERCENTILE($B$1:$B$10, 0.667))

Now THAT is analyzing data.

Color is for human eye reference, not formula referencing.  ;)

---------- FOLLOW-UP ----------

QUESTION: Hi Jerry!

thanks for your interest and input. I'm still having trouble...
I have a spreadsheet where I successfully counted cells of a specific color. I did so using a macro which I found on-line, named ColorFunction.

BUT when I imported some other database with colored cells and applied the above macro to count the colored cells, I get the same result every time, regardless of what color I'm trying to count. The cell shows no number; instead, it shows...
   
         #NAME?


Also, a dialog box appears with a statement:

      Ambiguous name detected:ColorFunction

You might think that this statement means that there no macro with the name ColorFunction is present in the file. But that is not the case, because no more than a few cells away, there is a very clear example of successful color counting using this macro.I'm thinking that if the macro were not there, there would be failure for all color counting. Truth to tell, while trying to "fix" things, I observed that even the cells which showed correct counts for various colors, would suddenly show the #NAME?
word instead of a count number. It is only by having saved the file before "fixing", and clicking "Don't Save", that I was able to retrieve the correct count of colored cells.
I'm going nuts trying to understand what's going on. If the macro works on some colored cells, why not on others as well?
Would it help if I could send you the file to look over? Maybe you can see the problem and fix it?

Thanks

Chris

Answer
I warned you before this would drive you nuts.  You should not be counting cells based on color, you should be counting cells based on the SAME formulas you used to conditionally color those cells.

Anyway...

"Ambiguous Name" means: "this function is found in more than one VBA module."

You've probably entered CountColor more than once and didn't realize it.  Remove the duplicate entry and that error will go away.


Now the bad news, I can tell you now that ColorFunction will not count cells that are colored by CONDITIONAL FORMATTING. I explained in the previous post the conditional formatting coloring parameters are not detectable by VBA.  It's invisible.  It won't work.  I promise.  Insert more "going nuts" here as you chase the counting color goal.

I want to help you succeed at counting the cells you want to count, but success on this will only occur when you understand the one point I'm trying to make and don't waste time on this.  CountColor will only count the colors of cells that have been manually colored specific colors.
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

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

Experience

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

©2016 About.com. All rights reserved.