Automatic Highlighting of nearest values based on one manually highlighted cell's value

[jsk123]

Please take a look at this image.

This spreadsheet contains a separate continuous sequence of numbers. Each sequence is present in a separate column and is completely independent from the other sequences.
What I will do is... I will highlight a value in the first column in yellow.
What I want Excel to do is... It should highlight the cells values (in blue) in remaining columns that are closest to the cell's value I highlighted in yellow

For example, please take a look at the image...
I highlighted 217.3 in Column A in Yellow. Now, In the remaining columns, B,C,D and E, the cells with closest value to 217.3 must be highlighted in e.g. Blue by excel .

How to achieve this? I want this to be able to dynamically update the sheet when I remove the highlight or add new highlights...

Please guide me...

 

[My Aswer Is This]

There is no auto sheet event script that will run when a cell color is changed.
Now this could happen when you enter the value into column "A"

In row three why do you have two cells colored "Blue" There can only be one value closest unless the values are the same.

 

[Eric W]

You could possibly take advantage of the double click event. Instead of highlighting the cell yellow, you would double click on it. The event handler would highlight that cell yellow for you, then find the closest values in the next columns and highlight them blue. If you double click on a cell that is already yellow, it would remove all highlighting. Let us know if that seems workable for your needs.

 

[My Aswer Is This]

ERIC:

OK I'm curious myself how would you determine the closest value. I maybe could come up with some complicated way but do you have a simple way to determine closest value? And since the OP has never come back I do not know why in row (3) he/she colored two values.

 

[Eric W]

The way I interpret his question is he wants to pick a number in one of the columns, and highlight it yellow. Then he wants the value closest to it in each remaining column highlighted in blue. So in his example above, he highlighted A3 (217.3). Now he wants 217.76 from column B in blue, also 216.71 from column C, 217.74 from column D, and 217.59 from column E. If he were to start with C14, he'd want A17, B16, D12, and E13 in blue.

As far as how to determine the closest value per column, I'd calculate the absolute value of the difference of the starting value, and all the values for a column, and choose the smallest absolute difference. I'm not sure of the best way to code that, since I haven't coded anything yet. As you noted, the OP hasn't returned to answer either of our questions. When that happens, I'll take another look.

 

[My Aswer Is This]

Thanks. I did not know how to do this myself so will be watching to see who comes up with a answer.

The way I interpret his question is he wants to pick a number in one of the columns, and highlight it yellow. Then he wants the value closest to it in each remaining column highlighted in blue. So in his example above, he highlighted A3 (217.3). Now he wants 217.76 from column B in blue, also 216.71 from column C, 217.74 from column D, and 217.59 from column E. If he were to start with C14, he'd want A17, B16, D12, and E13 in blue.

As far as how to determine the closest value per column, I'd calculate the absolute value of the difference of the starting value, and all the values for a column, and choose the smallest absolute difference. I'm not sure of the best way to code that, since I haven't coded anything yet. As you noted, the OP hasn't returned to answer either of our questions. When that happens, I'll take another look.

 

[jsk123]

Hello Eric... I'm not familiar with VBA. When you were talking about eventhandlers, I guess you are talking about VBA... So I'm clueless as to how to do that...

You could possibly take advantage of the double click event. Instead of highlighting the cell yellow, you would double click on it. The event handler would highlight that cell yellow for you, then find the closest values in the next columns and highlight them blue. If you double click on a cell that is already yellow, it would remove all highlighting. Let us know if that seems workable for your needs.

 

[jsk123]

Hi...

Please think of this problem in terms of "Columns" and not rows... Each column is an independent sequence... in Column C and Column D, the closest values to the original Yellow highlighted cell's value showed up in same row number.. It was just a coincidence..

There is no auto sheet event script that will run when a cell color is changed.
Now this could happen when you enter the value into column "A"

In row three why do you have two cells colored "Blue" There can only be one value closest unless the values are the same.

 

[jsk123]

As I mentioned in my first post, I will only highlight a cell in Column A... That is the only input I am giving to it... After that, Excel should figure out the value in the cell I highlighted and search for a cell in rest of the columns which contain a value equal to the value in originally highlighted cell or very close to it...

 

[jsk123]

Thank you for taking the time to review this... @MyAnswerIsThis and @EricW... Actually, I'm supposed to do this work manually. But I'm trying to automate this task and want to know if it is possible to do it without having to work with VBA... I'm familiar with conditional formatting based on formulas and the absolute value formula and other such handy functions....

If you can give me an idea on how to do this task using just formulas and conditional formatting, then it would be great.....

If you think it is possible, please tell me what formulas I would require to achieve this functionality.... At some stage I would have to use conditional formatting... that is pretty clear to me...

As mentioned earlier.. My only INPUT will be in column A. that's all... all blues are outputs....