Conditional formatting based on a formula

stefanaalten

Board Regular
Joined
Feb 1, 2011
Messages
71
Office Version
  1. 365
Platform
  1. Windows
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.
:confused::confused::confused:
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
:confused::confused::confused:
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!
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top