Conditional Format based on IF statement

[wring]

I would like every cell in column A formatted either Red or Green based on the following logic:

If the corresponding (same row) cell in column B is a 0 then a positive number in Column A should be highlighted/filled Red. If column B is 0 and Column A is negative number then Column A cell should be highlighted/filled Green.

Similarly if Column B is a 1, and Column A is positive, then Column A cell should be Green. If Column B is a 1, and Column A is a negative, then Column A cell should be Red.

See the below table for the logic I'm looking for with color coding.

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for your help.

 

[dreid1011]

Select your A column and make 4 new conditional formatting rules(use formula to determine format):

=AND(A1>0,B1=0) Format font color red
=AND(A1<0,B1=0) Format font color green
=AND(A1>0,B1=1) Format font color green
=AND(A1<0,B1=1) Format font color red

Or 2 rules:

=OR(AND(A1<0,B1=0),AND(A1>0,B1=1)) Format font color green
=OR(AND(A1>0,B1=0),AND(A1<0,B1=1)) Format font color red

 

[etaf]

If the corresponding (same row) cell in column B is a 0 then a positive number in Column A should be highlighted/filled Red. If column B is 0 and Column A is negative number then Column A cell should be highlighted/filled Green.

Similarly if Column B is a 1, and Column A is positive, then Column A cell should be Green. If Column B is a 1, and Column A is a negative, then Column A cell should be Red.

setup conditional formatting using the following formulas

What happens if zero ?

RED
=OR(AND(B1=0, A1>0), AND(B1=1,A1<0))

GREEN
=OR(AND(B1=1, A1>0) , AND(B1=0, A1<0))

Heres how to setup the conditional format

for 2007, 2010 or 2013 excel version
Conditional Formatting


Highlight applicable range >>

A1 : A1000



Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:



=OR(AND(B1=0, A1>0), AND(B1=1,A1<0))



Format… [Number, Font, Border, Fill] - format for RED
choose the format you would like to apply when the condition is true
OK >> OK

Set-up a new rule for GREEN

 

[Joe4]

=AND(A1>0,B1=0) Format font color red
=AND(A1<0,B1=0) Format font color green
=AND(A1>0,B1=1) Format font color green
=AND(A1<0,B1=1) Format font color red

Could also be done just using two Rules:

=OR(AND(A1>0,B1=0),AND(A1<0,B1=1)) Format font color red
=OR(AND(A1<0,B1=0),AND(A1>0,B1=1)) Format font color green

 

[dreid1011]

Could also be done just using two Rules:

=OR(AND(A1>0,B1=0),AND(A1<0,B1=1)) Format font color red
=OR(AND(A1<0,B1=0),AND(A1>0,B1=1)) Format font color green

Aye, noted and fixed while you posted, thanks!

 

[wring]

I tried using the two formulas you listed and no formatting occurred. Any idea maybe what I did wrong?

Under the 'Applies to' column in Rules Manager I have the entire column selected (A12:A76)

 

[dreid1011]

Did you change the rules to start at A12 and B12?

 

[Joe4]

Note that it also makes a difference if they are numeric entries or text (if the numbers appear left-justified in your cells, they are probably text and not numeric).
To reference text entries, you need to surround your values with double-quotes, i.e.
=OR(AND(A1>0,B1="0"),AND(A1<0,B1="1"))

 

[wring]

They were non-numeric. Once I put in quotations it worked great! Thanks for the help!