IF statement in conditional formatting

[rmccafferty]

Has anyone tried using an IF statement within conditional formatting? If so, I would like to know if it worked, and if so, was it straight forward or were there special issues to be dealt with, and how you set it up. I very much do not want to have to try to put it into VBA as it would be nightmarish to get it into all the cells and keep it working correctly.

I would like to use a formula something like the following in the conditional formatting formula.

IF(cell above = x, format a, format b).

Or better yet, I would like to use a nested IF statement. Something like:
IF(cell above = x, format a, IF(cell below=y,format b,format c))

I don't see how this can be done in the manage rules box of Excel 2007, but if it can be done, someone on this site knows how.

 

[Akashwani]

Hi,

What is it you want to do?
Do you have some sample data that I can be used?

I may be wrong, but isn't Conditional Formatting like IF?
If criteria is met then TRUE and apply CF if criteria is not met then FALSE don't do any CF.

Ak

 

[Marcelo Branco]

Hi,

I think you cant do this with only one rule (formula).

Try this (assuming A2 as target-cell)

Default format
format A2 as format c

CF>New rule>Use a formula to...
insert this formula
=A1="x"
Format button and pick format a

CF>New rule>Use a formula to...
insert this formula
=A3="y"
Format button and pick format b

Adjust the priority (format a must be the 1st rule)

HTH

M.

 

[Scott Huish]

I may be wrong, but isn't Conditional Formatting like IF?
If criteria is met then TRUE and apply CF if criteria is not met then FALSE don't do any CF.

Similar yes, but more correctly, Conditional Formatting applies the formatting if the expression evaluates to TRUE, but not if it doesn't evaluate to TRUE. In other words, not just FALSE, but error values, zero, etc. that don't evaluate to TRUE.

 

[T. Valko]

I may be wrong, but isn't Conditional Formatting like IF?
If criteria is met then TRUE and apply CF if criteria is not met then FALSE don't do any CF.

Similar yes, but more correctly, Conditional Formatting applies the formatting if the expression evaluates to TRUE, but not if it doesn't evaluate to TRUE. In other words, not just FALSE, but error values, zero, etc. that don't evaluate to TRUE.

In addition...

The "TRUE" value doesn't have to be a boolean TRUE. It can be ANY number other than 0.

For example:

=COUNTIF(A1:A5,"x")

That formula can return a number from 0 to 5. If the result is 0 then no formatting will be applied. If the result is ANY number other than 0 then the format will be applied.

Also of interest, formulas entered as cf criteria are automatically evaluated as array formulas. So, a formula like this would also work:

=OR(A1:A5="x")

 

[Akashwani]

Hi Hotpepper & Biff,

Thanks for the information, very helpful, especially this Biff....

Also of interest, formulas entered as cf criteria are automatically evaluated as array formulas. So, a formula like this would also work:

=OR(A1:A5="x")

I'm sure somewhere in the past I have done a CF like this....
=OR($A$1=2,$A$2=2,$A$3=2,$A$4=2)

Thanks for the tips, I hope the OP finds this helpful.

Ak

 

[T. Valko]

Hi Hotpepper & Biff,

Thanks for the information, very helpful, especially this Biff....



I'm sure somewhere in the past I have done a CF like this....
=OR($A$1=2,$A$2=2,$A$3=2,$A$4=2)

Thanks for the tips, I hope the OP finds this helpful.

Ak

You're welcome!