Previous Topic Next Topic

Conditional formating - apply on more cells

Solved/Closed
Biljana - Nov 30, 2008 at 07:18 PM
 Raj - Aug 30, 2017 at 10:13 AM
Hello,
i'm not so familiar with conditional formatting, so I would like to ask something:
I'm making a doc where Column A has number values, column B also numbers, of rank positions. Conditional formating of cell in column B would be: if number is greater than number in cell A, colour it Red, and if it is less than number in cell A, colour it green (rank position dropped down). Now, I managed that, but since the document has lots of rows, how do I apply the formating on the other cells in the column? If I make copy/paste to them, formatting doesn't apply on the whole column since it is not a function, but a cell format. And do I have to do the formating every day for every next column ?
Related:

3 replies

Answer 1 / 3
sheryljohnson
Posts
5
Registration date
Saturday November 1, 2008
Status
Member
Last seen
December 15, 2008
 4
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: 
=$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!
340
Poo
Jan 11, 2009 at 11:45 PM
Hi!!! Your guidance was really helpful... saved on a lot of effort!!! Thanks a ton!
0
iceteh
Jan 16, 2009 at 06:55 PM
Your replay is very very helpful. I had been looking for the solution. Thank you.
0
Alban
Jan 27, 2009 at 08:16 PM
Many thanks for this link.
It was really helpful
0
Outlandish
Mar 6, 2009 at 08:07 AM
Thank you so much. The answer I have been looking for all this time. You made my otherwise horrible week okay.

thanks.
0
TBone
Apr 6, 2009 at 12:08 PM
Thank you!! I cannot believe how long I had to search this morning to find a short, concise answer to a question that I feel shouldn't need asking. I just don't understand the thought process behind not having conditional formatting copiable across a range (relative) easily available.
T
0
Answer 2 / 3
Perinouk
Jul 31, 2009 at 03:25 AM
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
17
george
Mar 30, 2010 at 02:20 PM
you should try =AND(($G2>0), ($g2<21))
0
Womi
Sep 7, 2010 at 09:29 AM
Thanks! Just the thing I was looking for! All I did was change the 'and' to 'or' and got the results I needed.
0
Answer 3 / 3
Nagoor Meeran Haji
Dec 8, 2010 at 07:22 AM
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)
13