Conditional Formatting comparing one cell to another

ladygogo

New member
Joined
Oct 21, 2012
Messages
12
Reaction score
0
Points
0
How do I set conditional formatting for the cells in Column B so they turn a color when the value goes below the first Column (Column A)?


I tried all sorts of stuff, the only thing I could make work on ONE cell is to
1. select the individual cell,
2. click conditional formatting,
3. choose "classic" style
4. format only cells that contain
5. cell value
6. less than
7. and i chose the cell in column A.
8. Format, RED


I COULDN"T Apply this to the the whole column, what i tried to do is select the entire column B, set those conditional formats to that above and then select the entire column A - got an error saying that I might want to use a formula, blah blah blah and it wouldn't work.


I'm sure theres a simple way of doing this, I just can't figure it out! and there's no way I'm doing this on each cell individually.

Anyone know a formula I can just paste into the cell and copy it down the column? Or the steps in conditional formatting?

PS I have Mac Excel 2011
 

ladygogo

New member
Joined
Oct 21, 2012
Messages
12
Reaction score
0
Points
0
OK simply put
Column A Column B
10040
200100
300150
400200
600700
500200
9001000

How do I set conditional formatting so If Column B1<A1 Then Red, If Column B2<A2 Then Red and so on. You can't copy and paste the formatting like you can a formula, so how do i apply this condition to anything that gets entered in column B?

Help!
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
OK simply put
Column A Column B
100
40
200
100
300
150
400
200
600
700
500
200
900
1000

How do I set conditional formatting so If Column B1<A1 Then Red, If Column B2<A2 Then Red and so on. You can't copy and paste the formatting like you can a formula, so how do i apply this condition to anything that gets entered in column B?

Help!


follow these steps:

1. Select the range of cells in column B you want the formatting applied to
2. click conditional formatting ( under home tab )
3. select Highlight Cells Rules > less than
4. Enter =A1 in the source box on left side.
5. Click OK

You are done.
 

ladygogo

New member
Joined
Oct 21, 2012
Messages
12
Reaction score
0
Points
0
follow these steps:

1. Select the range of cells in column B you want the formatting applied to
2. click conditional formatting ( under home tab )
3. select Highlight Cells Rules > less than
4. Enter =A1 in the source box on left side.
5. Click OK

You are done.

woah, why did i make it so complicated.
 

ladygogo

New member
Joined
Oct 21, 2012
Messages
12
Reaction score
0
Points
0
you know what it was, i have a lot of blank cells in that row and it was applying the red formatting to those as well, so i added a first condition to "no fill with color format" if blank.I wish i could say If blank, don't apply the condition, but that doesn't seem to exist. Oh excel i love you but you're bringing me down.

Thanks tommy!
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
Here is a formula to not add format to the blank cells in your column. select your current range you have formatting set . go into conditional formatting and delete your existing rule(s)


1. Confirm your range in column B is selected
2. Click Conditional Formatting
3. Select New Rule ( located at bottom of the open box)
4. Select >Use formula to determine which cells to format
5. Paste this code into the source box =AND(B1<>"",A1>B1)
6. Click format button to set the fill and font color you want for your condition if it is met.

Done.
 
Last edited:
Top