Free Microsoft Excel 2013 Quick Reference

#N/A conditional format

This error message apears in my spreadsheet alot.


I would like to do a conditional format, so that if a cell equals #N/A then
it will grey out the font.

i have tried A1="#N/A" in conditional format but it did not work.

any clues?

Post your answer or comment

comments powered by Disqus
This error message apears in my spreadsheet alot.


I would like to do a conditional format, so that if a cell equals #N/A then
it will grey out the font.

i have tried A1="#N/A" in conditional format but it did not work.

any clues?

I'm almost done with my project, need one last thing!

I have five fields that have a conditional format applied to them: (see attached)

Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.

Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.

Confusing? Hope not! This is the last part of my project!

Thanks in advance!

I have a small glitch with a conditional formatting formula that I was once told could be solved by rounding. I can not find the information I was given so I must ask again for this help.

I have a column of times similar to bellow:

I am using conditional formatting to blank out the start and end repeated times as follows.

Cell A2: Cell Value is equal to =A1 (then dark blank out)
Cell A3: Cell Value is equal to =A$1+A2-A3 (then dark blank out)
_______Cell Value is equal to =A2+A3-(A$14+TIME(0,0,1)) (then dark blank out)
Cell A4: Cell Value is equal to =A$1+A3-A4 (then dark blank out)
_______Cell Value is equal to =A3+A4-(A$14+TIME(0,0,1)) (then dark blank out)
...and so on.

When it works properly I would only see the times in cells A1, A3 to A12 and A14.
When the glitch occurs I would see the times in cells A1, A3 to A14.

Because of the 0:00:01 (needed so the formatting will DARK BLANK Cell A1 and A2) the dark blanking of A13 and A14 sometimes does not work depending on the times in the cells. Most of the time it works fine, but there seem to be rare situations that the formatting doesn't work.

As I said before there was someone on a board much like this one that told me to ad a rounding function to the formula, but as with many functions in Excel I'm not fluent enough with it to know how to apply it.

Please help

I've created a spreadsheet for tracking real estate. In one column is the original asking price. In the next column is the new asking price if it has been reduced. Other columns use this information to do calculations. I have used the IF Function in some of these columns to look in the new asking price column first, if there is no value to use the original asking price column.

My question is can I use an IF Function in a conditional format. The reason I want to do this is in other columns The cell is currently Conditional Formatted to turn Grey if the result is greater than the asking price and turn Green if it is equal or less than the asking price. If there is a new asking price I currently have to manually change the Conditional format to look in the new column and I would like to automate it which I have been able to do with the IF Function. The cells that are formatted do not use either of the original or new asking price columns, but their result is compared to them

If this is not possible could you suggest another way.



Is it possible to use a VLookup within a conditional format Formula is: area. I just gave it a whirl and couldn't figure out how to insert the current cell value as the Lookup_value.



I am trying to figure out how to make a cell interactive so that when it is clicked, or double clicked, it triggers a conditional format, such as changing the cell color. I know that I have seen something like this before, it was an NCAA march madness bracket in a spreadsheet. When a team name was double-clicked, the name appeared on the next bracket, which could then be double-clicked itself and so on. If anyone could let me know what kind of function this is or where I can find more information it would be greatly appreciated. Thanks in advance for your help.

I need a conditional format formula in Cols B:D that will change color of font if the digit is an odd digit.

******** ******************** ************************************************************************>Microsoft Excel - CASH 3 FREQUENCY.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)boutA21=
ABCD2109/25/079352209/24/071622309/23/073572409/22/07868DIGIT REPEATS
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I have had trouble posting this, forgive the disappearance and reappearance of it.

This is complicated. I need help with a conditional formatting statement. Here is the situation. I have a number in B45. It changes based on a reading taken.

B42 gets one of three statements in it. VML, CLK_Hold or CLK_Setup. Now in B24 I have a number that B45 has to be greater than if B42 = VML. In D24 I have a number that B45 has to be greater than if B42 = CLK_Hold. And I have in F24 I have a number that B45 has to be greater than if B42 = CLK_Setup.

One more problem. The user will change the numbers in B24, D24 and F24 based on other conditions before executing the macro that creates the number in B45.

If B45 is less than the corresponding number and condition, the cell has to appear red with bold white letters. So I need help writing the conditional format equation to do the following:

If B42 = "VML" then if B45 is less than B24 and B45 does not equal "", then B45 (shows up red with white letters), else If B42 = "CLK_Hold" then if B45 is less than D24 and B45 does not equal "", then B45 (shows up red with white letters), If B42 = "CLK_Setup" then if B45 is less than F24 and B45 does not equal "", then B45 (shows up red with white letters).

Any takers? I am going to keep trying, but intervention by any of you would be GREATLY appreciated.



What I have is have a spreadsheet with multiple cell containing data. Some
of the data is retreived by a formual and some is due to a manual input of
numbers. I would like to have a conditional format that would shade the cell
if someone writes over the formula with a value.

Any help would be greatly appreciated.

I have an Excel spreadsheet in which I set up a conditional format that
changes the color of an entire row when one of the cells has a value greater
than 0. I want to copy this format throughout the sheet. I can't find a way
to do it except by formatting each row individually. Every way that I've
tried does not allow the formula to be in a series which means that when
there is a value in the first cell the entire sheet changes color. Any ideas
would be appreciated. Thanks.

I have the followint formula in a conditional format.
I remember getting it from this group, it highlights the active cell in
I cannot delete it and I believe it will not allow me to copy and paste
data about the spreadsheet.
I need to delete as I need to copy and paste more than I need the
active cell highlighted.

I have several rows of data with a header row that has a conditional format
applied depending on a row below.

When I copy that header row to another sheet, the conditional formula still
refers to a row which is no longer where it expects it to be. (because I
copied the header row somewhere else).

Is there anyway that I could get the 'result' of the conditional formula and
NOT have the formula itself ?

ie Can I copy a conditional formula as copy paste special value ?

Message posted via

I have a conditional format that highlights the protected ( locked) cells in
a different colour.

How can I save this format as the default format to be used automatically
when I start a new worksheet

regards fred from south africa
Think of the trees
- use both sides of the computer paper

please forgive any mistakes as I am not massively proficient in this but...

I am trying to use a conditional format 3-colour scale to colour dates for when things such as insurance policies are due for renewal. I would like any cells with a date of within 2 months of today to be red, 2-3 months to be orange or whatever, and more than 3 months to be green.

I have tried to set the rule using formulas but cannot get it to work - it accepts the formulas but no colours appear.

The three formulas I used were:

1. =TODAY() - this worked fine when the other 2 were set to percentages



The only other alternitive I tried was setting cells to have those values and trying to get the formula to use them, but excel told me you cannot "use relative references in conditional formatting for colour scales".

Is it just not possible to do this? or are my formulas not doing what I think they are?

any help would be much appreciated!

Is it possible to set a conditional format in a cell which has a formula
so that when a value appears in that cell, the cell background changes
colour. When there is no value the default colour should remain as the
formula returns a "blank" value. When I try it the conditional format
assumes the formula is a value and shows a colour.
Value any assistance.

Turriff, Scotland

I am using a conditional format that highlights cells B1,C1 & D1 if cell A1 has an x in it. Is there a way to drag (or otherwise) this forumla down so that I don't have to re-write it on each row??

Many thanks!!!

I need to copy a Conditional Format where the "condition" compares two
cells on a row. When I use the Paste Format function, all references
point to the original comparative cell instead of the relative cell
by row. The spreadsheet is quite large and is updated daily. Any
hints? ~ Barbara

Hi all,

I'm trying to look up a conditional format value in a formula to show the difference between the cell value and the conditional format value. The spread sheet shows number of hours required in a month, the conditional format is set to show text in green if the cell value is less than or equal to the total hours available in the month, and red if it is greater than. The conditional format value of the current month I change weekly when I import the forecast pivot table as there is less time in the month as each week passes.

Here are the values I use depending on how many weeks there are in a month (everything is based on how many Friday's there are in a month just to make life easier)
1 Week = 666.9
2 Weeks = 1333.8
3 Weeks = 2000.7
4 Weeks = 2667.6
5 Weeks = 3334.5

So if I have 3 Friday's left this month my conditional format says if the cell value is less than or equal to 2000.7 then the text is green, if the cell value is greater than 2000.7 then the text is red. The cell value is a simple formula adding all the hours required for that line in that month.

Now what I'd like to do is have a value in the cell below which basically says what the difference is between the actual cell value and what the conditional formats value is.

For example, if the cell value was 2100.7 I would like the cell below (where I'm putting this formula) to look at that the difference between that value and what the conditional format value of that cell is and return that figure, so in this case it would be -100.

Does that make sense?

Thanks in advance.

I am attempting to compare multiple fields in a conditional formatting
formulat but am not sure how. For example, the following generates an error
when I attempt to save it:

=IF($F2="",,TEXT(TODAY() and $L2="PSR","yyyy-mm-dd")>$F2)

Lets say based on a value in A5 I would like to change the text format for
cells d5:g5 (four columns), what would be the best procedure to do this, can
it be done using conditional format if each cell d5,e5,f5,g5 or is there
another way. By that do I input a conditional format for each cell
d5,e5,f5,g5 (if A5> "xx" then <format>.

A big Thank You for all the help.


Can I do a conditional formatting on a range if the value
in one cell in the range = X

in other words can I format the back ground for line 5 if
C5 = X

is there a way i can get excel to automatically copy a cell (when it becomes highlighted due to a conditional format) to a separate worksheet?

excel should "paste special" a "conditional formatting"

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

How can I apply a conditional format to a different cell? For example: I
want to format cell B1 based on the value in cell A1. So if the value in A1
was = 1, then I would fill B1 w/ light grey. If A1 were = 2, then I would
not fill B1 at all.

Any ideas?


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