Conditional formatting to highlight the closest value

[craig oxo]

My scenario:

I have a row of a range of values across 10 columns, on the 11th column is my actual figure, or the data that is appropriate

say:

100,90,80,70,60,50,40,30,20,10 - my figure being 37

I need the cell containing '40' to highlight without any other cell changing, and i just cant get my head around it!!!

Submitted via EggHeadCafe
SharePoint Tip / Thought of the Day WebPart
http://www.eggheadcafe.com/tutorials/aspnet/14280ff8-3c9f-46bd-8214-9267e613c8ec/sharepoint-tip--thought-of-the-day-webpart.aspx

[joeu2004]

On Feb 25, 1:33 am, craig oxo <oxo...@hotmail.com> wrote:
> I have a row of a range of values across 10 columns,
> on the 11th column is my actual figure, or the data
> that is appropriate say:
> 100,90,80,70,60,50,40,30,20,10 - my figure being 37
> I need the cell containing '40' to highlight without
> any other cell changing

For the example given, assuming 100,...,10 are in A1:J1 and 37 is in
K1, then select A1:J1 and enter the following Conditional Formatting
formula ("Formula Is"):

=ROUND($K$1,-1)=A1

then select the desired CF Format.

Note that this CF formula works only if A1:J1 are multiples of 10. Do
you want a formula that will work any arbitrary values in A1:J1?

Also note that if A1>=105 or A1<5, nothing will be highlighted. Do
you want a CF formula that will highlight the appropriate endpoint in
that case?