Menu
  1. Accueil
  2. Tech Forum
  3. Office Software

Conditional Formatting with 3 cell [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hi all

I want to do conditional formatting a cell but based on the criteria set by 2 other sells in the row

so if A1 = 0 and B1 > 0

i want C1 (or whatever cell) to say Yes or something?

what VBA can I use?


thanks in advance

5 replies

Answer 1 / 5
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 764
I dont get it. Where is formatting involved in this. It seem that all you want is to have C1 populated based on A1 and B1

=IF(AND(A1=0, B1>0), "Yes", "No")
Answer 2 / 5

i feel like an idiot thanks alot for your help
Answer 3 / 5
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 764
how do you know how an idiot feels like :P

You are welcome and thanks for being polite enough to post feedback. There are only few who take pains to do that and for that I thank you.
Answer 4 / 5

lol not the first time

anyways could i ask you another question?

i got the 2 cells A1 and B1

i want to populate C3 with a "yes" if they are BOTH >1 and a "no" if they are both = 0
Answer 5 / 5
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 764
First to teach you how to fish :)

if statement is like this

=IF(condition, show this when true, show this when false)

you can have up to 7 nested if

=IF(condition, true, IF(condition 2, true, false))
=IF(condition, if(condition 3, true, false), IF(condition 2, true, false))

for conditions you can use AND and OR if there are multiple condition
a1=5, and b4=10
=IF(AND(A1=5, b4=10), true, false)

a1=5 or b4 = 10
IF(OR(A1=5, b4=10), true and false)

NOW for your question
=IF(AND(a1>1, B1>1), "yes", IF(AND(A1=0, b1=0, A1<>"", B1<>""), "No", ""))
this says if both a1and b1> 1 then show yes
if both a1 and b1 = 0 and not equal to "" then show no. The reason for check for "" is if cell is blank, a1 will be evaluated as 0, so in case it is not desired
if neither conditions are met then show ""

You could have written same as (which is harder to understand)

=IF(A1>1, IF(B1>1, "Yes", ""), IF(A1=0, IF(A1<>"", IF(B1=0, IF(B1<>"", "no", ""),""),""),""))
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!