Free Microsoft Excel 2013 Quick Reference

Conditional formatting Excel 2007 Color Scales for Text


I'm trying to use Excel 2007's Conditional formatting color scales, but I would like to have the numbers within the cells 'disappear' or match the color of the background. Any advice would be appreciated!


Post your answer or comment

comments powered by Disqus
In Excel 2007, new feature of conditional formatting is the "icon set" in
which you can assign an icon on a particular value or range of values. i just
what to know how does the default condition of icon set work? by default,
excel is using the 67% and 33% for 3 iconset but i want to know 67% of what
or 33% of what? how did they compute for that? how did they get it or how did
they compute it? i tried several ways of computing it using the 67% and 33%
but still it doesnt fit the default conditional formatting for icon set. is
it the 67% or 33% of the total of the range or what? im really confused. i
really need to know the answer coz for sure my students will ask me about it.
hope someone can help me about this...

thanks! ^_^

I have a sheet that calculated percentages. I want to set up a conditional format color scale where 0% is green, 50% is yellow and 100% is red.

However, every time I use the three-color scale and percents, it applies to color scale based on the range of values present, not the full 0-100 range. So for example, if I have three cells with 5% 15% and 30%, then 5% will be green, 15% will be yellow and 25% will be red. If it were working in the way I would like, they would all be various shades of green and yellow green. I don't want to compare the values to each other, I want them compared to the range 1%-100%. Any ideas?

Also, can you do a color scale that changes the font color and not the cell color?


Can I customize my own set of conditional formatting icon in Excel 2007? If so, how?
thanks in advance.


I am new to this forum. This is what I am struggling with.

Attached is an excel sheet with an example of what I am trying to do.

If value in column A is not equal to the value in Column B, then either Column B or Column C should get the color Red. Can I do by using a formula in Conditional Formatting?

I tried using IF statement but dont know how to pick a color in the formula. I am a newbie, so please help me.

Hi All

Quick question, I want to know how to use conditional formatting to highlight a cell from a certain point onwards?

For example

On my sheet i have an IF statement on cell A2, if it is true then i want CF to fill in the cell A2 and onwards to a specific colour.

How can I use CF to do this

thank you so much

Just installed 2007 and like it - would like to use conditional
formatting to highlight cells based on if the cells equal a group of
numbers - actually it is a worksheet containg all the office pool
powerball tickets so I want to go through the worksheet and highlight
the winning numbers - so I need to be able to have something that says
if a cell in the range = 1 or 2 or 3 or 4 or 5 etc. Can it be done?


I've tried, but I can't figure it out. How to use conditional formatting to
alternatively color rows based on values in a column?

For example, in B13:B3000, I have values like this:

Col B Row color I'd lilke
Apples Green
Apples Green
Pears White
Pears White
Pears White
Pears White
Bananas Green
Monkeys White
Monkeys White
Giraffes Green etc...

Thanks for your help...

I've tried, but I can't figure it out. How to use conditional formatting to
alternatively color rows based on values in a column?

For example, in B13:B3000, I have values like this:

Col B Row color I'd lilke
Apples Green
Apples Green
Pears White
Pears White
Pears White
Pears White
Bananas Green
Monkeys White
Monkeys White
Giraffes Green etc...

Thanks for your help...

Morning all,

Here's a stupid New Year's Oops!

I've had my Excel 2007 set up for the longest time to show the 30 most recent files when clicking on the office button. After changing it to 50 the office menu in Excel runs off the bottom of the screen and I can no longer get ot the options at the bottom to change it back.

Does anyone know the registry key to modify this setting back to the 30 that allowed the full menu to be visible?


I need to color (fill) a row of cells based on the value in one column in that row. For example, my worksheet may contain data in A1 thru F20. For each row, based on the value (text) in column D, I want to select a color to shade all of the cells in that row (columns A thru F). I tried conditional formatting but there were two issues, 1) I need more than 3 colors) and 2) it only shaded C10 cell that contained the search value, not the entire row. I'm using Excel 2003.

I know I need to use VB but have only used it once in the past with very detailed instructions (how to get in and out, plus the code) from a board member on a different topic. I searched the posts but can't get close enough to my needs to come up with the solution.

Thanks in advance for your help.

I am trying to learn how to (if possible) make conditions in Excel 2007 so that graphs' colors will vary based on a value. For example, the closer the number is to 100%, the darker the red (or whatever color), the further it is (lowest), the lighter it gets. I figured out how to change basic colors on a bar graph by using the if/then formula and overlapping the series', but I don't know how I'd adjust shading based on values. I think this can be done using VBA, but I have never touched VBA before and have no idea where to start. Does anyone have any tips, websites, or books they would recommend?

[Excel 2003] I have a complicated formula which returns either a +/-number, or one of several text messages in a cell. I am trying to add conditional formatting to the cell. If the cell returns a number, I want to add three conditions so that the cell is red, yellow, or green based on the amount of time before/after the due date of a project. If the cell returns a text message (e.g., "Closed"), I want the cell to default to white (the normal background color). I thought I could do this since I understand conditional formatting allows you three colors, plus the default when a condition is not met (thus four). I had hoped that "text" would not meet the conditions I set which are based on numbers. The conditional formatting seems to be recognizing the text as a number though, because when the cell formula returns text, the background is red (same condition as items >0 past due date.

My goal is that:
if projects are "Closed" (non-numerical text should default to white);
if project is overdue (numerical cell value >0), red;
if project is due within 30 days (cell value between 0 and -30), yellow;
if project is not due for 31+ days, green.

The numbers are based off a formula which looks at the due date versus today() to calculate a numeric value for # days overdue.
My conditions are:

1. Cell value is greater than 0 (red)
2. Cell value is between 0 and -30 (yellow)
3. Cell value is less than or equal to -31 (green)

Any suggestions would be greatly appreciated. I do not know how to use VBA so I am trying to do this with formulas and/or formatting.

Thank you.

Thanks in advance to all those who answer.

Before i ask what i need to know i will clarify some things i already know to help out those who answer.

1. I know that you can conditionally format Excel to change the background color if it meets a value.

2. I know that you can change the formatting of a cell in column A based on a value in Column B.

3. I know that you can only set 3 conditions in Excel 2002.

4. I know you can set unlimited conditions in Excel 2007. (i dont want to get 2007)

5. I know that you can use VBA code and apply it in excel 2002 in order to create more conditions.

MY QUESTION IS:how can i get VBA code that can add conditions (more than 3 per column) to a work sheet that allows conditioning of cells in Row B based on values of Cells in Row A.

If you could supply a VBA code that i could copy that would be great.

I am also open to purchasing an excel plug in for 2002 that allows unlimited format conditions.

And an open to any other suggestions you may have.

Thanks for your help.

Hello Everyone,

I have recently upgraded to office 2007. I am now attempting to utilize the conditional formatting functionality... with little sucess. The result I am looking for is to change the text color to red or blue in column C. I wish to use a comparison check between cells A and B as my determining factor for the text color. The problem I am having is when I apply the rule all the cells take on only one color despite the validity of the comparison check. For example. (A1 = 3, A2 = 4, B1 = 2, B2 = 7) format cells using a formula: = $A$1 > $B$1, text = blue. In this case all the text turns blue in the C column because the comparison does not cascade to the next row where A2 is not greater than B2, it continues to use the comparison from the first row.

Is there a method for cascading the conditional formatting function down to apply for each unique row, without using a macro?



Hi Guys

I posted this question the other day but failed to explain myself properly.
I am using excel 2007 and have the following basic spreadsheet

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF6=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I have used Conditional formatting--color scales to shade in the percentage column. I wondered if there was a way to carry this formating on to the other 2 columns?




Conditional formating feature in Excel helps to brighten up the worksheet
and makes analysing data a lot easier and less stressful.

May I know how to conditional format repeated bands of colors in the
folowing example ?

Eg 1:
A ... M

1 Green
2 Yellow

E.g 2
A ... M

1 Green
2 Yellow
3 Red

E.g 3
A .... M

1 Green
2 Yellow
3 Red
4 Blue

Kindly show me the Formula 1, 2, 3 and 4 in the dialog box for each example
and the area needs to be highlighted before formating.

Many Thanks



Excel 2003 does not allow references to other worksheets for Conditional
Formatting criteria. Does Excel 2007? Thank you, JP.

Hi everyone,

I would be grateful if someone could help me with a problem related to format painting conditional formatting to other cells in Excel 2007.

The problem comes from continually having to use Excel 2007 at home to work on spreadsheets for work which are 2003 versions. The compatibility conflicts invariably come from overlapping conditional formatting, and part of the problem is having only just discovered what seems to be (maybe its something I don't understand) a bug in 2007?

In Excel 2003, if you painted a format from one cell to another, it completely replaced whatever format was there, including the conditional formatting.

In 2007, if you paint a format onto a cell, it doesn't replace the conditional formatting, but merely adds to the conditional formatting which was already there .... even if it's the exact same conditional formatting! As an example, if I paint a format from A1 to B1 that says if the cell says "Yes" colour it red, and I paste the format into that cell 8 times, in the 'Manage Conditional Formatting' there will be eight copies of the exact same conditional formatting saying if the cell says "Yes" colour it red.

To use the paint metaphor, when I paint a door, I like to take the all the paint off back to the bare wood, not continually paint on top of the old paint

Just wondered if there was any option to change the way format painter works in 2007 so it works the same as it does in 2003?

Thank you everyone/anyone for any help you can offer.

Hoping someone can help with this. I have created a simple conditional format whereby the text in one cell has white (invisible) text unless someone answers the question in another cell incorrectly, in which case the text turns red so the user can see it. This seems to work fine if I save the file as .xlsx and reopen in Excel 2007. However, if one of my teammates opens the file in Excel 2000 or 2003, the formatting doesn't work. I can look at the formatting rule and it appears like it should work, but it doesn't. I tried saving as Excel 97 - 2003 format, but then the formatting doesn't even work for me when I reopen in 2007. The weird thing is that if you open the file, see that the formatting isn't working, and then explicitely change the font in that cell to white (even though it already appears to be white), the formatting starts working.

I'm attaching a sample file and would appreciate any thoughts on how to get around this issue. (At least I think I attached the files?)



I think I have found a bug in Excel 2010 conditional formatting and wondering if anyone else can confirm, or even better, provide a workaround.
My version is 14.0.6023.1000 (64 bit)

The problem relates to Conditional Formatting with Color Scales.

When copying below a range to extend it, rather than extending the conditional formatting range, it creates a new range.

If the initial range extends to the new cells below, it works unless you are copying more than one column,each with independent color scales set for different sub ranges.

This can be reproduced as follows:
in A1 type 1in A2 type = A1+1copy this down to row copy this across to column B
You should now have 1 to 8 down column A and 1 to 8 down column B.
Now set a conditional format color scale for the range A1:A10 (includes two blank rows)Now set a conditional format color scale for the range B3:B10 (note that we start here two records below the other one)Now select cells A8:B8 adn copy the two cells down.
I have attached my file where I have done the above steps.

You will note that the conditional formatting now shows independent rules for the individual copied cells rather than incorporating them into the standard rule previously set up. In fact it has removed them from the other range.

Very frustrating behaviour.

Any ideas on how to overcome this? I have tried reporting to it Microsoft but have not found the right channel.

I'm new to Excel 2007, and I'm having some problems with conditional formatting. I have two columns of data, N and I. Some of the cells in column I are empty/blank, and the conditional formats are in column N. For example, if I2*1.05 is greater than the value of N2, I want N2 to be boldfaced red. If I2 is empty/blank, I want the format of N2 to be unchanged. The first conditional format, "if I2*1.05 is greater than N2, then the N2 value is boldfaced red" is easy, but what is the syntax for "if I2 is empty/blank, then leave N2 formating unchanged?"

Thank you.

dears all,

I'm looking for vba code able to do this:

on the same sheet and on the range A1:A10 there are the thresholds as numbers (formula colculated). Starting form the range N1:N10 toward right, there are cells filled with text (formula claculated )and to be formatted (font/background color) comparing the text lenght and the threshold.

The formatting formula for cell N1 should be LEN(N1)>A1 to get N1 text colored differently. the formula for N2 should be LEN(N2)>A2, for N3 should be LEN(N3)>A3, and so on.
The macro should copy these conditional formattings, increase the cell referenced by LEN() and paste it to the adjacent range so that the conditional formatting becomes LEN(O1)>A1 for O1, LEN(O2)>A2 for O2, LEN(O3)>A3 for O3, and so on.

I need to embedd this code into a macro I have that already returns an offset pointing to the column to copy. The offset variable name is HereItGoes. This means that every time the macro is executed, the range to copy and paste is shifted by one column toward right.

kind regards


I am automating an invoice template for an excel novice, thus I want to conditionally format certain cells dependant on what text is in them.

I want to do this in VB but I don't know how!

Any help would be appreciated.


Below is a piece of the worksheet I am currently using. I am using conditional formatting to change colors based on column I. If "approved" then green, if "Awaiting App" Then red. Now when I am done with each line I have a macro that takes it off this list. The problem is I have to keep setting up conditional formatting every time a line is knocked off, I am wondering if there is a way that I can apply global formatting based on what is typed into column I. So every cell is controlled by one format setting. Any help is much appreciated. Thanks.

******** ******************** ************************************************************************>Microsoft Excel - Unique Print Schedule (version 1).xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG6L6=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

No luck finding an answer? You could always try Google.