# Cell highlighted in a given range

#### pucha

##### Member
Dear friends,

I have a range of price value (stock), where i am using two conditional formatting "between & inbetween" option to highlight a column range. Now i want to highlight the close price (single cell) on the same range as the third conditional formatting. how do i proceed for the same.

open high low close
1000 1200 950 980

range of high and low
high 1200
low 950

range (price movement between 1200 and 950
1200
1150
1110
...
...
...
1000
980
950

Now the close is at 980. so the cell I want to highlight is 980.

With Regards

Pucha

Hi, pucha!
Regards!

#### Attachments

• Sample.xls
32 KB · Views: 3
What's the tolerance range? Or is it if Close is between any consecutive cell values?

If between any consecutive cell values then CF formula is.
=OR(MEDIAN(\$J\$6,\$L2,\$L3)=\$J\$6,MEDIAN(\$J\$6,\$L1,\$L2)=\$J\$6)

It should be closet to the cell value. I have attached another file please check.

It should be closet to the cell value. I have attached another file please check.

#### Attachments

• Sample_1.xls
32 KB · Views: 1
Try this then.
=OR(MAX(IF((\$L\$2:\$L\$26-\$J\$6)<0,\$L\$2:\$L\$26-\$J\$6))=\$L2-\$J\$6,MIN(IF((\$L\$2:\$L\$26-\$J\$6)>=0,\$L\$2:\$L\$26-\$J\$6))=\$L2-\$J\$6)

But it showing anything.

Not showing anything.

Did you apply the format? See attached.

#### Attachments

• Sample_1.xls
40 KB · Views: 6
Thank you Chihiro. It is working . I was applying directly in formula. Thanks once again.

Well is it possible to highlight the nearest number in the range instead of two cell. if possible please let me know.

thanks
With Regards.

What do you mean?

Do you mean that you want only single cell, which has smallest variance from Closing value highlighted? (be it positive or negative)

yes, the number very close to the the value j6.

Then just use...
=MIN(ABS(\$L\$2:\$L\$26-\$J\$6))=ABS(\$L2-\$J\$6)

EDIT: Forgot the ABS to right of equal sign.

Last edited:
Exactly. Thank you very much. It has solved my problem.

With regards
pucha