VBA highlight closest values in a range

[newvbaer]

Hi everyone,

I have been trying to figure out a code that would highlight the two closest values of a range selection. What I mean by this is the two closest values (.9999 and .9998 for example) anywhere in a given range and be the two numbers must be adjacent in the range.

I am very new to VBA and I am having a hard time. I was wondering if anyone knew how to do this?

Thank you in advance for your help.
The idea is to find the 2 closest values in the whole table and highlight them. They can be far appart.

 

[Anthony47]

A possible solution:

Sub Closestsss()
Dim DArr(), myRan As String, MaxClos As Long
'
myRan = "K2:K20"    '<<< Value range
MaxClos = 2         '<<< How many highligth
'
ReDim DArr(1 To Range(myRan).Rows.Count)
For i = 1 To UBound(DArr)
    If Range(myRan).Cells(i + 1, 1) <> "" Then DArr(i) = Abs(Range(myRan).Cells(i, 1) - Range(myRan).Cells(i + 1, 1))
Next i
Range(myRan).Interior.ColorIndex = xlNone
For i = 1 To MaxClos
    hpos = Application.Match(Application.WorksheetFunction.Small(DArr, i), DArr, False)
    Range(myRan).Cells(hpos, 1).Interior.ColorIndex = 2 + i
Next i
End Sub

Lines marked <<< need to be adapted to your situation

Bye

 

[Fluff]

Cross posted on multiple sites.

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply the links.