ExcelBanter - Conditional formatting to highlight the lowest valid value

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Conditional formatting to highlight the lowest valid value (https://www.excelbanter.com/excel-worksheet-functions/181296-conditional-formatting-highlight-lowest-valid-value.html)

Conditional formatting to highlight the lowest valid value

Cell A1 contains a "critical value". I want to format the range B1:B5 so that
one cell is highlighted - the cell with the value closest to, but not greater
than, A1.

E.g. if B1:B5 looks like this:

ColB
3
9 <--- This should be highlighted when A1 = 14
16 <--- This should be highlighted when A1 = 16
58 <--- This should be highlighted when A1 = 30
100

Thanks.

Conditional formatting to highlight the lowest valid value

Nevermind this post; I figured it out. I could swear I had tried this formula
and it didn't work... I must have mistyped.

=ROW()=MATCH($A$1,$B$1:$B$5)

"PaladinWhite" wrote:

Cell A1 contains a "critical value". I want to format the range B1:B5 so that
one cell is highlighted - the cell with the value closest to, but not greater
than, A1.

E.g. if B1:B5 looks like this:

ColB
3
9 <--- This should be highlighted when A1 = 14
16 <--- This should be highlighted when A1 = 16
58 <--- This should be highlighted when A1 = 30
100

Thanks.