# Conditional Formatting - Highlight the closest value to a number

#### speeder2k3

##### New Member
Hello everyone!

I need your help for the following example situation.
I have a column of values, lets say from A1 to A5 (numbers with 3 digits after coma) and the cell B1 with the value 2.255:
A1 - 0.856
A2 - 1.245
A3 - 1.455
A4 - 2.250
A5 - 2.260

I want to highlight the cell from A1 to A5 which is closest to B1. If there are two cells equally distant to highlight both.
According to B1=2.255 I should highlight both A4 and A5.
If the B1 value changes to lets say 1.188 then A2 should highlight, being the closest.
I need to do this only with conditional formatting formula. I cant alter the structure of the file and insert another columns to help in the process.
Thank you very much!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Hi,

you could apply as new rule for conditional formatting in A1:A5

=ABS(A1-\$B\$1)=MIN(ABS(\$A\$1:\$A\$5-\$B\$1))

Cheers

Thanks Canapone, that worked.
How about if I need to highlight one closest value on 5 separated columns?
Example: A1:A5, C1:C5, E1:E5, G1:G5, I1:I5 are the columns. The numbers in the columns are sorted ascending starting with A1 the smallest to I5 the highest (this probably it is not important).
We are assuming the previous example and looking for the closest value of B1.

I tried the formula which you gave me in this situation but I got an error saying that there are too many arguments for this formula. While selecting each column with the mouse+CTRL the formula turned to:
=ABS(A1-\$B\$1)=MIN(ABS(\$A\$1:\$A\$5,\$C\$1:\$C\$5,\$E\$1:\$E\$5,\$G\$1:\$G\$5,\$I\$1:\$I\$5-\$B\$1))

I tried to select all the cells between the A1 to I5 and use the formula but in the columns B, D, F, G and H there is text so it doesn't highlight anything (if I delete the text it works but I cant alter the structure of the table).

Thank you.

Hi,

you could apply as new rule for conditional formatting in A1:A5

=ABS(A1-\$B\$1)=MIN(ABS(\$A\$1:\$A\$5-\$B\$1))

Cheers

Hi,

I'm trying this formula as new rule (delete the old one)

=ABS(A1-\$B\$1)=MIN(ABS(\$A\$1:\$A\$5-\$B\$1),ABS(\$C\$1:\$C\$5-\$B\$1),ABS(\$E\$1:\$E\$5-\$B\$1),ABS(\$G\$1:\$G\$5-\$B\$1))

applied to

=\$A\$1:\$A\$5,\$C\$1:\$C\$5,\$E\$1:\$E\$5,\$G\$1:\$G\$5

Hope it helps

Canapone this is very helpful. Thank you very much for your help.

Hi,

thanks for providing kind feedback.

Cheers

Replies
3
Views
56
Replies
3
Views
219
Replies
5
Views
246
Replies
1
Views
261
Replies
3
Views
274

1,203,538
Messages
6,055,992
Members
444,839
Latest member
laurajames

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back