Compare five values

[Guest]

Hello,
I have an excel file that will be made up of clusters of five numbers. I
want a macro to check if 3 of those 5 numbers are within 0.1 of each other.
If yes highlight those three numbers green. If not highlight all five numbers
red.
Thank you for your help.

 

[Guest]

How about if 4 of the numbers are within 0.1 :

1.000
1.001
1.002
1.003
99.99

or if all 5 numbers are within 0.1:

1.000
1.001
1.002
1.003
1.004

 

[Guest]

Hello,
sorry yes, I forgot to specify that at least three of the numbers must be
within 0.1. If there are more than 3, I suppose I would like the 3 closest
highlighted but that is not critical. Thank you
Tyra

 

[Guest]

This is going to be in a worksheet with many clusters of five numbers. I
tried setting it up as a and/or function, but I exceeded the 1024 character
limit in the formula. I figured I need to program a loop to do this but don't
really know where to start. I appreciate any help you can give me.

 

[Guest]

This is just an example. Consider five cells (A1 thru A5)
We must example the triplets in these cells. Fortunately there are only 10
triplets in a group of five cells:

1,2,3
1,3,4
2,3,4
1,2,4
1,4,5
2,4,5
3,4,5
1,3,5
2,3,5
1,2,5

The following code examines each of the triplets to see if the values are
"close together". As soon as a good triplet is found, the cells are marked
and the routine finishes. If no good triplet is found, no cells are marked:

Sub tyre()
' gsnuxx
Range("A1:A5").Interior.ColorIndex = xlNone
i = Array(1, 1, 2, 1, 1, 2, 3, 1, 2, 1)
j = Array(2, 3, 3, 2, 4, 4, 4, 3, 3, 2)
k = Array(3, 4, 4, 4, 5, 5, 5, 5, 5, 5)
For l = 0 To 9
v1 = Range("A" & i(l)).Value
v2 = Range("A" & j(l)).Value
v3 = Range("A" & k(l)).Value
d1 = Abs(v1 - v2)
d2 = Abs(v1 - v3)
d3 = Abs(v2 - v3)
m = Application.WorksheetFunction.Max(d1, d2, d3)
If m < 0.1 Then
MsgBox (l)
Cells(i(l), "A").Interior.ColorIndex = 36
Cells(j(l), "A").Interior.ColorIndex = 36
Cells(k(l), "A").Interior.ColorIndex = 36
Exit Sub
End If
Next
End Sub