# Conditional formating - apply on more cells

Solved/Closed
Related:

- Apply conditional formatting to the selected cells
- Apply conditional formatting to the selected cells so cells with a value greater than 10 - Best answers
- Apply conditional formatting to the selected cells with a value greater than 10 - Best answers
- Apply conditional formatting to the selected cells so cells with a value greater than 10 are formatted using a yellow fill with ✓ - Forum - Office Software
- Conditional formatting based on another cell; apply to rows ✓ - Forum - Excel
- Conditional formatting excel if another cell contains text ✓ - Forum - Excel
- Excel conditional formatting based on another cell text ✓ - Forum - Excel
- Conditional formatting greater than another cell - Guide

## 3 replies

sheryljohnson

Updated on Nov 29, 2018 at 03:32 PM

- Posts
- 5
- Registration date
- Saturday November 1, 2008
- Status
- Member
- Last seen
- December 15, 2008

Updated on Nov 29, 2018 at 03:32 PM

The "secret" is to make the formula with "relative referencing", hence, if your formula looks like this:

then remove the $ sign before the row number, so it will look like this:

With this, you actually don't need to copy the conditional formatting. You can select an entire range before applying it, and insert the formula as if you selected only the first cell at the top. Excel will know to apply the correct formulas to all the other cells in the range (assuming the range is a column).

Or you can apply this formula to the first row, and copy it with the format painter.

Good luck!

=$B$2>$A$2

then remove the $ sign before the row number, so it will look like this:

=$B2>$A2

With this, you actually don't need to copy the conditional formatting. You can select an entire range before applying it, and insert the formula as if you selected only the first cell at the top. Excel will know to apply the correct formulas to all the other cells in the range (assuming the range is a column).

Or you can apply this formula to the first row, and copy it with the format painter.

Good luck!

Hi,

This is great. However I would like rows highlighted which are within a range. I tried the formula =($G2>0), ($g2<21) but it isn't working. what's the right formula to use?

Thanks

This is great. However I would like rows highlighted which are within a range. I tried the formula =($G2>0), ($g2<21) but it isn't working. what's the right formula to use?

Thanks

Color Row based on value in a Column (#row)

Select all cells (Ctrl+a) (foobared in Excel 2003 hit Ctrl+A twice)

menu: Format, Conditional Formatting, Formula is on drop down box

Condition 1: =$D1<=1.00 [interior Color 35 Light Green]

Condition 2: =$D1<=1.05 [interior Color 36 Light Yellow]

Condition 3: =$D1>1.05 [interior Color 38 Rose]

Since the conditions are checked in order it is frequently unnecessary to have more than one comparison in a condition. For example in Condition 2 above, we do not need to write the condition as

=AND($D1>1.00,=$D1<-1.05)

Select all cells (Ctrl+a) (foobared in Excel 2003 hit Ctrl+A twice)

menu: Format, Conditional Formatting, Formula is on drop down box

Condition 1: =$D1<=1.00 [interior Color 35 Light Green]

Condition 2: =$D1<=1.05 [interior Color 36 Light Yellow]

Condition 3: =$D1>1.05 [interior Color 38 Rose]

Since the conditions are checked in order it is frequently unnecessary to have more than one comparison in a condition. For example in Condition 2 above, we do not need to write the condition as

=AND($D1>1.00,=$D1<-1.05)

Jan 11, 2009 at 11:45 PM

Jan 16, 2009 at 06:55 PM

Jan 27, 2009 at 08:16 PM

It was really helpful

Mar 6, 2009 at 08:07 AM

thanks.

Apr 6, 2009 at 12:08 PM

T