Free Microsoft Excel 2013 Quick Reference

Conditional formatting blank cells

hi,

its very easy question but I just dont know how, pls help!
I wanna conditional format range of cells if they are blank - turn yellow,
otherwise red. But I tried [ Cell is - equal to - ""] doesnt work.
Pls adv.

thx


Post your answer or comment

comments powered by Disqus
hi,

its very easy question but I just dont know how, pls help!
I wanna conditional format range of cells if they are blank - turn yellow,
otherwise red. But I tried [ Cell is - equal to - ""] doesnt work.
Pls adv.

thx

Hi all,

I'm having trouble with a conditional format. I'd like a particular cell to turn red if another cell on the same sheet (sheet 1) is blank. Problem is, the cell I'm referring too (II2) is a formula looking on another sheet. Even though the cell has pulled a blank from sheet2, it still contains the formula and the Conditional Format doesn't seem to like this.

I can't remove this formula so can I make the Conditional Format ignore it and just check the cell?

Cheers

Hi all.

As a pert of a spreadsheet I have 2 columns (M5:N400) of 400 cells deep. Each cell in these columns contains a formula that counts days, when appropriate. They can end up showing a value of blank, a number between 0 and 5, or any number greater than 5. (But very rarely greater than 30)

If the number is greater than 5 then it means that it has exceeded it's target time and I wish to highlight this with conditional formatting (CF).

I have a CF on all cells in both columns that says...

If "The cell value" is "Greater than" "5"... then
format the background colour to a pale yellow.

It works, but doesn't work.

If the cell value is 0 to 5 it's unchanged. Good.
If the cell value is >5 it changes. Good.
BUT, if the cell is blank it also changes. Bad!!!

I assume it is because it is looking at the fact that there is a formula in the cell, even though the displayed value is "". So I tried a second level saying that if the cell is equal to "" then no format, but it still changes.

Has anyone got a clue, please? Thank you.

I want to use three colours to highlight a spreadsheet, red for numbers less
than -1, amber for number between -1 and 0, and green for numbers greater
than 0 which I can do no problem. Each cell that I am colour formatting has
the formula (or similar):
=IF(G2="","",IF(M2="","",M2-G2))

However the blank cells are also being coloured in green when I want them to
remain white. Any ideas?

Hey all,
I am trying to have conditional formatting of cells apply only if another cell is blank in 2007.

A3 has a formatting of =MOD(ROW(),2)=1, then =AND(A3<>"",A3+15<=$G$1) to change the color based on date and finally =AND(A3<>"",A3+30<=$G$1) to again change the color again by date.

What I would like to do is for the cells in columb A to change only if corresponding columb B cell is empty, without using macros if possible.

I want to use three colours to highlight a spreadsheet, red for numbers less
than -1, amber for number between -1 and 0, and green for numbers greater
than 0 which I can do no problem. Each cell that I am colour formatting has
the formula (or similar):
=IF(G2="","",IF(M2="","",M2-G2))

However the blank cells are also being coloured in green when I want them to
remain white. Any ideas?

Hi, I have read the previous threads on this subject, however I can't
find a response to my need. I am trying to apply conditional formatting
to cells using a macro. However my range of data changes daily.

What I would like to do is apply the conditional formatting to blanks
in column F, ONLY if the cell next to it in Column E is NOT Blank. That
way I can select the column as part of the macro, and not have to worry
about my Blank cells beyond my data range being conditionally
formatted.

All help appreciated

Thanks

I have been trying to Conditional Format a cell that has 6 different color options for formating. The formulas I have used for VBA will work as long as I type the data into the cell I want to change color on. The problem I am having is that the cell I want to format the color of is the result of a formula. How do I get the conditional formatting to react to the calculated cell? Any help would be greatly appreciated.

Thanks..

I need to conditionally format a cell if it is less than or equal to the value in the cell below it . However, if there is an "NA" in the cell below it, then the cell is formatted - as if the "NA" is a 0 (and thinking the formatted cell is less than the cell below it.) How do I set up the conditional format to format the cell if the cell below it is less than or equal, but "NA" (or any other type of text)?

Thanks.

HI guys

It's been a while. Is it possible to conditional format a cell which is offset from the one you are currently entering? i.e if A1 is being entered, depending on what value is in it, B1 then turns green? If it is possible is it then possible to colour a range of cells in the same fashion please?

Hi,

does anyone how I can use conditional formatting for cell swhich do have dependents and or precedents?

Thx in advance

Hi!
I noticed that in Excel 2007 and 2010, if you select a range with numbers and go to Conditional Formatting > Highlight Cell Rules and choose any numerical comparison operator (Greater Than, Less Than, Equal To), in the RefEdit control Excel would suggest you some default value. Any ideas where does this value come from?
Apparently, it is a function of the range, but I positively can't figure out what is this function. It is not an average, as some articles in the Net would suggest. BTW, googling did not help either...
I have attached the Excel file with some arbitrary figures. You can check what I am trying to explain.

Last note - I run my Excel 2007 in a compatibility mode, if this helps.
Any ideas or suggestions are welcome. Thanks in advance.

How can I conditionally format a cell based on the value in a different cell.

I have two columns of information. None of the information in the cells I am
trying to format is numeric. I am trying to track names to loans that are
with the company. If they wrote outside, I need to keep track of which other
company it was written with.

The First column currently has a conditional format to highlight the cell if
the value is = "Home". The other values that are entered are variations of
"Outside". I have to keep the variations as they are, in order to track which
loans went with which other company.

The second column has the borrowers name. I need this column to highlight
the cells if the loan was written outside.

So if the cell is highlighted in column A, it should not be highlighted in
the column B.
And Vice versa.

Is it possible to have more than 3 conditional formats per cell. I am
creating a chart which I would like the cells to change color dependant on
the letter inserted.

I have a Excel 2003 spreadsheed with conditional formatting: a cell changes
from red, to green, to yellow, depending on the value entered into the cell
-- it works fine. When the spreadsheet uploads into SharePoint 3.0 (published
with spreedsheet functionality), the colored cells all change to a light red
color. What must I do to keep the color assignments in the Excel spreadsheet
so they appear the same when viewed in SharePoint?

Is this even possible? I've done conditional formatting to a cell based on
that same cell's info BUT what I want to do now is something like this...Can
I apply conditional formatting to cell A3 based on the information in cell
B3? If so, how do I do that?? I completely suck at writing formulas so, if
you know the answer, can you help me with how to write the formula, too?
Basically, I want to apply formatting to A3 if B3 has a value greater than
zero. Thank you!!

How do I set the conditional formating for cell D2 based on the value in cell
G2?

Does anyone know a formula to conditionally format a cell to change color when a date is entered into another cell?

Specifically, I have a date in one cell that turns red when it is 7 days past. I want that cell to then turn to green when I enter a completion date in another cell.

Anyone?

how does one do this?

i know how to conditional format, but not with formulas.

I went to Conditional Formatting - Highlight Cell Rules - More Rules - Use a formula

but didn't know where to go from there. For formula I entered =if(A2=A1) and left the formula at that, and then underneath pressed the format button to make the preview which color I wanted. But the formula itself is erroneous.

Can anyone help a novice? I'm trying to conditional format a cell based a result in any of 5 other cells.

I want Cell A1 to remain normal if the number in that cell does not match any of the numbers in cells F1:F5 (or if there aren't any numbers in cell A1 or F1:F5). But if the number in Cell A1 matches any of the numbers in cells F1:F5 (and there won't be duplicates) I want Cell A1 to change to a red background.

I've tried some of the basic formulas that I know without much luck. I was hoping there would be someone out there where this wouldn't take much effort and they were willing to help.

Any comments are appreciated.

Conditional formatting: Highlight cells in a column that contain 1 or more of a set of words.

Below: I want all cells in colum B that contain the words "CAT", "MONKEY", or "BULL" highlighted regardless of other punctuation or words in the cell.

I found the following formula but it does not work:
=IF(ISERROR(IF(LEN(B1)>0, FIND(","&LOWER(B1)&",", ",cat,monkey,bull,"),#VALUE!)),"=",B1)

Please see attached spreadsheet.

A B
1 CAT TIGER
2 MAN
3 CHICKEN, DONKEY854
4 DUCK" SNAKE
5 MAN
6 SNAKE, MONKEY 2658
7 MONKEY: TIGER
8 MAN(
9 DOG, BIRD 4564
10 BIRD, SNAKE
11 TIGER, DONKEY
12 MAN
13 LION, MONKEY
14 BULL, LION
15 DONKEY, TIGER
16 MAN
17 COW, SNAKE..4
18 PIG, COW
19 MAN
20 OWL, MONKEY

Notice some words are not separated by spaces or are followed by or preceeded by punctuation (if the word is in complete I want the cell to be highlighted regardless of what is before or after).

I am using Excel 2003, and was told the above formula should be entered in the cell vale/equal to....

Thank you,
Daniel

Hello

Does anybody know how to set the conditional formating to cells with formulas?

I have x2 cells (let's call them A & B) with long, complicated IF AND formulas and values in them are different depending on what i type in other cells linked to them

I have set the conditional formatting 'if not between cells a & b, highlight in red' , but it doesnt work at all. I think it doesnt see the cells as values and freaks out seing the formula

HELP!

All:

I am attempting to use either a user-defined function in VBA or a
spreadsheet function to conditonally shade a cell.

I would use Excel's conditional formatting; however, it is limited to
three conditions.

Also, I can do this with VBA. Unfortunately, then I would have to
re-refernce the subroutine when the data is moved. Given the dynamics
of this spreadsheet, I would be required to do this often.

Is there anyway to use something like this,IF(A1=1, ColorIndex=1, "") ,
to conditionally format a cell?

Thanks in advance.

Floyd

conditional formatting works very well with numbers. Is there a way to
conditionally format a cell that contains words? For example if the cell
contains "confirmed" would be green and "decline" would be red - works. What
about if the cell conatins "confirmed" but also something else in that cell?
Thanks


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