Two values highlight

[dineshchaure26]

In column A, suppose I have the below values

100.456
100.789
101.000
101.245
101.675

Now I want to use a formula that will highlight the immediate highest and lowest values from the column for a given number.

For example if the number that needs to searched is 100.987, then 100.789 and 101.000 must highlight since 100.987 falls between these two numbers in the column.

Please help me with the formula that I can use in conditional formatting.

 

[Phuoc]

Book1
	A	B
1		Search number
2	100.456	100.987
3	100.789
4	101
5	101.245
6	101.675
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A6	Expression	=A2=AGGREGATE(15,6,$A$2:$A$6/($A$2:$A$6>$B$2),1)	text	NO
A2:A6	Expression	=A2=AGGREGATE(14,6,$A$2:$A$6/($A$2:$A$6<$B$2),1)	text	NO

 

[dineshchaure26]

Book1
	A	B
1		Search number
2	100.456	100.987
3	100.789
4	101
5	101.245
6	101.675
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A6	Expression	=A2=AGGREGATE(15,6,$A$2:$A$6/($A$2:$A$6>$B$2),1)	text	NO
A2:A6	Expression	=A2=AGGREGATE(14,6,$A$2:$A$6/($A$2:$A$6<$B$2),1)	text	NO

Thank you much

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You could highlight them both with one CF rule.

21 12 16.xlsm
	A	B
1		Search number
2	100.456	100.987
3	100.789
4	101.000
5	101.245
6	101.675
Above Below

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A6	Expression	=OR(B$2=MEDIAN(B$2,A2,A3),B$2=MEDIAN(B$2,A1,A2))	text	NO