Related:

- Apply conditional formatting to entire column
- Excel apply conditional formatting to entire column - Best answers
- How to apply conditional formatting to entire column - Best answers
- Excel apply conditional formatting to entire column - How-To - Excel
- Conditional Formatting: Font color for entire column based on another column ✓ - Forum - Excel
- Conditional formatting of entire column ✓ - Forum - Office Software
- How to apply conditional formatting to multiple columns ✓ - Forum - Excel
- Excel Conditional Formatting Rows to Columns ✓ - Forum - Excel

## 3 replies

sheryljohnson

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

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!

View all 27 comments

Report

NKL

To the person that asked the right question and gave that answer THANK YOU!

Report

L-man

Thanks, you are a life saver.

Report

DMak

This is awesome! Thanks for your help!

Report

dan

Thank you, that worked !

Report

Raj

Thanks much...

Perinouk

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

Nagoor Meeran Haji

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)