Conditional formatting based on a formula

[stefanaalten]

How can I format a cell based on a value being something other than one of two values? Specifically, I have data in five columns ($P$32:$Q$709,$S$32:$AA$709) where each cell can be one of "y", "n" or something else, e.g. "s", "p", "f", "?", etc.

I would like to format cells with "y" in green text, cells with "n" in red text, and cells with something else with blue text.

To evaluate the condition for an individual cell, e.g. P32, is easy:
IF(OR(P32="y",P32="n"),FALSE,TRUE)

But how do I do this for my range of data - should I replace the cell reference with a placeholder/variable of some kind? I'm using Excel 2007.

 

[T. Valko]

How can I format a cell based on a value being something other than one of two values? Specifically, I have data in five columns ($P$32:$Q$709,$S$32:$AA$709) where each cell can be one of "y", "n" or something else, e.g. "s", "p", "f", "?", etc.

I would like to format cells with "y" in green text, cells with "n" in red text, and cells with something else with blue text.

To evaluate the condition for an individual cell, e.g. P32, is easy:
IF(OR(P32="y",P32="n"),FALSE,TRUE)

But how do I do this for my range of data - should I replace the cell reference with a placeholder/variable of some kind? I'm using Excel 2007.

You have more that 5 columns there, you have 2 separate multi-column ranges.

The easiest way to do this is to apply the formatting to each range separately.

Select the *entire* range P32:Q709 starting from cell P32.
Cell P32 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Enter this formula in the box below:
=P32="Y"

Click the Format button
Select the Font tab
Set the text color to a shade of GREEN
OK
OK

New rule>Use a formula to determine which cells to format

Enter this formula in the box below:
=P32="N"

Click the Format button
Select the Font tab
Set the text color to a shade of RED
OK
OK

New rule>Use a formula to determine which cells to format

Enter this formula in the box below:
=AND(P32<>"",P32<>"Y",P32<>"N")

Click the Format button
Select the Font tab
Set the text color to a shade of BLUE
OK out

Repeat this process on the other range S32:AA709.

This was much easier in earlier versions of Excel!

 

[stefanaalten]

Thank you!

 

[T. Valko]

Thank you!

You're welcome!