Multiple conditional formatting rules across multiple ranges?

excel excel-formula conditional-formatting
Louise Brain picture Louise Brain · Feb 11, 2014 · Viewed 66k times · Source

I need to do the following. Highlight a row if cell E contains a number greater than 30 AND cell L contains a number greater than 100. This rule needs to be applied to all rows. Can you help please?

Answer

Dmitry Pavliv picture Dmitry Pavliv · Feb 11, 2014

You should use CONDITIONAL FORMATTING:

1) Select all cells in the sheet (by pressing on the top left corner):

enter image description here

2) With selected range go to "Conditional Formatting -> New Rule.."

enter image description here

3) Select "Use formula..." rule type, enter formula =AND($E1>30,$L1>100). Choose desired format and press "OK"

enter image description here

RESULT:

enter image description here

Related questions

Conditionally formatting if multiple cells are blank (no numerics throughout spreadsheet )

I have created a spreadsheet in Excel and am attempting to use Conditional Formatting to highlight a cell or row if any or all of the cells in the last four columns are blank. My columns consist of name of …

Read More
Conditionally formatting cells if their value equals any value of another column

I have data in the A and B columns. B column's data is mostly duplicates of A's data, but not always. For example: A Budapest Prague Paris Bukarest Moscow Rome New York B Budapest Prague Los Angeles Bukarest I need …

Read More
Conditional formatting using AND() function

I'm trying conditional formatting on a sheet. I need to fill the cells with a certain color according to the following conditional statement: =AND((INDIRECT(ADDRESS(4;COLUMN()))>=INDIRECT(ADDRESS(ROW();4)));(INDIRECT(ADDRESS(4;COLUMN()))<=INDIRECT(ADDRESS(ROW();5)))) When I …

Read More