Conditional Formatting

[helenmeyer]

Hi all and thanks in advance for your excellent help

I've applied conditional formatting to an area of my spreadsheet so that when all cells containing N/A are coloured green. However, when I applied this rule not all the cells in my selection that contained N/A were coloured green. All the cells have the same format.

Any help would be appreciated

Thanks

 

[Joe4]

Re: Conditiional Formatting

What is your exact rule? Is it a formula? If so, please post it.
And how have you applied it to the whole range?

 

[helenmeyer]

Re: Conditiional Formatting

Thanks for the reply. Yes I selected the entire area before putting the condition in. I'm using the following formulas:

=IF(OR(AND(b1<=TODAY()-1825,b1>1),(b1="TBA")),TRUE)
=IF(AND(b1<=TODAY()-1642,b1>1),TRUE)
=IF(OR(AND(b1<=TODAY()-1095,b1>1),(b1="TBA")),TRUE)
=IF(AND(b1<=TODAY()-915,b1>1),TRUE)
and then the N/A

 

[AlanY]

Re: Conditiional Formatting

does CF

=ISNA(A1)

not work for you?

 

[Joe4]

Re: Conditiional Formatting

=IF(OR(AND(b1<=TODAY()-1825,b1>1),(b1="TBA")),TRUE)
=IF(AND(b1<=TODAY()-1642,b1>1),TRUE)
=IF(OR(AND(b1<=TODAY()-1095,b1>1),(b1="TBA")),TRUE)
=IF(AND(b1<=TODAY()-915,b1>1),TRUE)
and then the N/A

Are those the order you have the various CF rules in?
What color is associated with each rule?
Do you have the"Stop If True" option set for any of these?
The "N/A" values that aren't working, are they coded in, or the result of a formula? If a formula, please post the formula.

Note that with Conditional Formatting rules, since AND and OR function return boolean results by default, you do not need to wrap them in an IF function,
i.e. you can simplify each like this:
Use

[COLOR=#333333]=OR(AND(b1<=TODAY()-1825,b1>1),(b1="TBA"))
[/COLOR]

instead of

[COLOR=#333333]=IF(OR(AND(b1<=TODAY()-1825,b1>1),(b1="TBA")),TRUE)[/COLOR]

 

[helenmeyer]

Re: Conditiional Formatting

Hi - no it didn't work for me.

 

[Joe4]

Re: Conditiional Formatting

Hi - no it didn't work for me.

That doesn't really help us to help you!
Please answer ALL the questions I posted in my last reply.

 

[AlanY]

Re: Conditiional Formatting

That doesn't really help us to help you!
Please answer ALL the questions I posted in my last reply.

i guess that probably was responded to post#4