Highlight Closest Over/Under Values in Horizontal Range

D

DixiePiper

New Member
Joined
Oct 19, 2015
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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:

ABC
D
E
F
1Ref. ValueLOWMIDHIGH
2$135$111$124$139$157$173

<tbody>
</tbody>

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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi, welcome to the board.

There are probably lots of ways of doing this.

Are you values on each row always sequenced as shown, i.e. lowest value in col B, highest value in col F, etc, and only ever 5 values in total per row ?
 
Upvote 0
Hi, give this a try in the conditional formatting section:
=MATCH($A2,$B2:$F2,1)=COLUMN(B2:F2)-1
It should highlight the lower bound. Changing it to
=MATCH($A2,$B2:$F2,1)=COLUMN(B2:F2)-2
should highlight the upper bound in a different colour. It's quite specific to your problem in terms of it being defined by the number of columns and stuff, but play around with the match less than and greater than function.
 
Upvote 0
Gerald Higgins said:
Hi, welcome to the board.

There are probably lots of ways of doing this.

Are you values on each row always sequenced as shown, i.e. lowest value in col B, highest value in col F, etc, and only ever 5 values in total per row ?
Click to expand...


Oh yeah I forgot to ask that, my solution is dependent on them always being formatted that way.
 
Upvote 0
Gerald, thank you for chiming in.

Yes, the values are always sequenced as shown and there are only ever 5 values per row. FYI, the listed Col/Row are for reference only. This is part of a much larger table. Essentially it's a long list of labor categories with corresponding market data. I want to be able to compare the desired salary (Col A in the example) with the percentiles from the survey (Col B-F in the example) and highlight where Col A value falls in reference to the Col B-F data points.

My preference is for the most elegant and stable solution. I am not a programmer by trade and while I'm the primary user of this workbook, there may be occasion when it falls into other hands. I have gotten my feet wet in VBA (although it's been a few years since my last project) and I can generally figure things out with a bit of assistance and a lot of resolve.
 
Upvote 0
dmhexcel said:
Hi, give this a try in the conditional formatting section:
=MATCH($A2,$B2:$F2,1)=COLUMN(B2:F2)-1
It should highlight the lower bound. Changing it to
=MATCH($A2,$B2:$F2,1)=COLUMN(B2:F2)-2
should highlight the upper bound in a different colour. It's quite specific to your problem in terms of it being defined by the number of columns and stuff, but play around with the match less than and greater than function.
Click to expand...

dmhexcel, I appreciate the input but it didn't seem to work; I'm not sure why. The only thing I changed were the Col/Row references to match my worksheet which shouldn't impact anything. I wondered if it was an issue with the reference value as formula but using a hard-keyed value didn't work either. Am I missing something?
 
Last edited:
Upvote 0
Let me add that while I'm not highly skilled with VBA, I am fairly proficient in Excel.
 
Upvote 0
Changing the column reference will change things. I would change it to column(A#:LastCol#) and remove the -1 then if you want to make it work. How is your actual sheet formatted? because this will work for a sheet that looks like this:
A B C D E F
$135 $111 $124 $139 $157 $173
120$111 $124 $139 $157 $173
125$111 $124 $139 $157 $173
123$111 $124 $139 $157 $173
160$111 $124 $139 $157 $173
140$111 $124 $139 $157 $173
170$111 $124 $139 $157 $173
180$111 $124 $139 $157 $173

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Hey I made a change to the original thing I posted. Try:

=MATCH(LookupValue,LookUpRange,1)=COLUMN(StartOfTable)

Start of table shouldn't have any $'s in it, should be something like A2
This should identify the lower bound.

=MATCH($A2,$B2:$F2,1)=COLUMN(A2)

(take note of the dollar signs and stuff though)
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
macro for formatting columns if values are greater than or equal to reference cells
Replies
7
Views
429
Peter_SSs
Peter_SSs
V
  • Solved
Conditional Format - COUNTIF Range to a Criteria Range
Replies
5
Views
517
Peter_SSs
Peter_SSs
ajjava
  • Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
831
ajjava
ajjava
C
  • Question
Conditionnal Block & Formatting of part of a range under certain conditions
Replies
0
Views
329
Cornstarch
C
K
  • Question
Change Conditional Formatting formula for all rules in cell
Replies
3
Views
451
KrisKiel
K

Forum statistics

Threads
1,214,907
Messages
6,122,185
Members
449,071
Latest member
cdnMech

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top