Excel: Highlight Closest Over/Under Values in Horizontal Range

I have searched and searched on this and if there is an answer, I've either not found the magic combination of words to conjure the solution from the depths of the internet or cannot understand my own search result. As such, apologies for any redundancies. I have a dynamic table in which I want to include some basic data analysis. The number of rows is variable based on data imported via VBA. I have a script to copy the conditional formatting so I only need a solution applicable to a single row. I need to be able to highlight the closest over/under values to a given number. My table is set up as follows:

1 Ref. Value LOW MID HIGH
2 $135 $111 $124 $139 $157 $173
The end result for the above example would be highlighting applied to C2 and D2, representing the closest under (C2) and the closest over (D2). Caveats and Assumptions:
  • my initial read is I should be able to accomplish this with two separate conditional formatting rules
  • there will be instances where the reference value is outside the parameters of the range; no formatting here is acceptable as I can identify that elsewhere
  • my goal is a visual "at a glance" idea of where the reference value falls on the scale.
Advance thanks for any help.

This question generated 13 answers. To proceed to the answers, click here.

This thread is current as of January 26, 2018.

For more resources for Microsoft Excel