# Conditional Formatting using criteria select which cells

#### polarisking

##### Member
Range A2:A6 contains an integer value
Range B2:B6 contains a blank or N value

The objective is to highlight the cell in Range A2:A6 having the minimum value, but ONLY using those cells in Col A where its corresponding Test value is blank. So, in this example, the result of the formula s/b 2 since the value of 1 is excluded based on a Test value of N.

ValueTest
1N
2
3
4
5

#### GraH - Guido

##### Well-Known Member
Maybe, as Conditional Formatting rule
=MINIFS(\$A\$2:\$A\$6;\$B\$2:\$B\$6;"")=\$A2

or when that function is not available try
=MIN(IF(\$B\$2:\$B\$6="";\$A\$2:\$A\$6))=\$A2

#### polarisking

##### Member
Maybe, as Conditional Formatting rule
=MINIFS(\$A\$2:\$A\$6;\$B\$2:\$B\$6;"")=\$A2

or when that function is not available try
=MIN(IF(\$B\$2:\$B\$6="";\$A\$2:\$A\$6))=\$A2
What an impressive, straightforward solution. One change, the semicolon needs to be a comma, like so
=MIN(IF(\$B\$2:\$B\$6="",\$A\$2:\$A\$6))=\$A2

Thank you, so much!

#### GraH - Guido

##### Well-Known Member
Not on my end, I'm using Belgium settings. I normally change those when I copy solutions, here I simply forgot. ;-)

#### Peter Bartholomew

##### Well-Known Member
Precisely the same solution but slightly different in appearance:
The conditional format is based on the formula
=CF?
where CF? is defined to refer to
= MINIFS(Value, Test, "") = @Value

Moving back in time CF? might refer to
= MIN( IF(Test="",Value) )=(this Value)
where I have used 'this' to provide the intersection of the current row [ 7:7 or similar spreadsheet junk] and the column, 'value'.

#### pecoflyer

##### Active Member
What should be highlighted if the "blank" cells contain empty text strings ("") instead of being completely empty ??
As you posted an image (sheets are ALWAYS recommended) the contents of colB cannot be entirely defined.....