Conditional formatting that extends over a range of data

[Mountain99]

Hi

I want the outcome to be true when two cells have certain conditions met....but then this needs to repeat down the columns.

I will try to make it clearer:

The formula is contained in D1. So D1 shades if the below condition is met:

If A1 = x and B1 = y then D1 shads.

So I have:

=AND(A1="x",B1="y")

However I want D1 to shade:
IF A2 = "x" , B2="y"
And A3 = "x" , B3 = "y"
AND A4 and B4 etc etc...

Many thanks

[L-Drr]

You could use the CONCATENATE function in your CF and check if A2A3A4 equals "xxx" etc.

[Mountain99]

I am I am not following. If your formula fixes the below please be a bit more detailed

IF

A1 = Blank and B1 = Blank
A2 = Blank and B2 = Blank
A3 = Blank and B3 = Blank
A4 = Blank and B4 = Blank
etc...
Then D1 = No shading

However if for example:
A1 = Blank and B1 = Blank
A2 = Blank and B2 = Blank
A3 = "x" and B3 = "y"
A4 = Blank and B4 = Blank

Then D1 = shading

[L-Drr]

Ok we're not on the same page on what combination of values should result in the shading of D1.

Is this it?
Shade D1 if every pair of cells (like A1 and B1, or A2 and B2) is blank&blank OR x&y
Do NOT shade D1 if one or several of the pairs are not blank&blank or x&y

[romperstomper]

Use a formula for the CF:
=COUNTIFS($A$1:$A$10,"X",$B$1:$B$10,"Y")>0

[Mountain99]

Hi sorry

If A1 and B1 or any pair of cells in the column of A and B have the value x and y then shade d1...if all the values in column A are Blank and all the values in Column B are blank then don't shade D1. But it is important that there is the X&Y relationship to make D1 shade. We can't use a concatenation because x is actually a number.

So what i'm saying is that if A1 = Nil and B1 has a value of 0 to n then shade D1.

This actually works:
=AND(A1="NIL",B1="12345")

the D1 shades

BUT I need it to work for 183 rows down the column...remember. X and Y are variables.

So x is actually the word "Nil" and Y could be any integer

[romperstomper]

=sumproduct((isnumber($a$1:$a$10))*(isnumber($b$1:$b$10)))>0

[Mountain99]

Hi thanks for all the suggestions.

I still don't think you are understanding what i need.

I have attached an example.

[romperstomper]

Try:
=COUNTIFS($B$2:$B$10,"Nil",$C$2:$C$10,">0")

[Mountain99]

This works perfectly. If I wanted to add in an additional outcomes of "<Enter amount>" how would i go about doing this?

=COUNTIFS($B$2:$B$10,"Nil",$B$2:$B$10,"<Enter amount>",$C$2:$C$10,">0").

This does not work.

[romperstomper]

Unfortunately you can't use array constants in CF formulas so a simple solution would be:
=COUNTIFS($B$2:$B$10,"Nil",$C$2:$C$10,">0")+COUNTIFS($B$2:$B$10,"<Enter amount>",$C$2:$C$10,">0")

[Mountain99]

That worked! Thanks

[romperstomper]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Mountain99]

Apologies.

I just noticed that target cell remains shaded when other values exist in B2....referring back to the example

If "NIL" OR "<Enter amount>" is selected it turns red. However if the value of "> $1.0m potential P&L impact" or "< $1.0m potential P&L impact" is selected the cell remains red.

[romperstomper]

Whoops - change the last section of the formula to this:
COUNTIFS($B$2:$B$10,"=<Enter amount>",$C$2:$C$10,">0")

[Mountain99]

Unfortunately thats no doing it. Now target cell shades only when B = Nil.

[Mountain99]

Unfortunately thats not working. Now target cell only shades when B = "Nil"

[romperstomper]

It works for me.

[Mountain99]

=+COUNTIFS($B$2:$B$10,"Nil",$C$2:$C$10,">0")+COUNTIFS($B$2:$B$10,"*=*<Enter amount>",$C$2:$C$10,">0")

This is what I have?

[Mountain99]

Have attached my test.

[romperstomper]

Remove the asterisks.

[Mountain99]

Ha ha....I did think the asterisks looked odd but honestly didn't think to test. Thanks. Resolved

[romperstomper]

I'm not really sure where you got them from.

[Mountain99]

Oh that's really interesting. I'm on my iPhone and I reread the thread and I notice that you inserted the = sign and made it blue.... However on my PC the equals sign does not display as a bold blue but rather an = sign with asterisks around it! "SOLVED"