any ideas?
Before the code, here are the general steps:
1. split the list into separate cells in a helper column
2. sort the helper column
3. calculate the difference between adjoining cells
4. look for the minimum difference
5. color by numbers
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then
Exit Sub
End If
n = Split(Target.Value, ",")
Application.EnableEvents = False
Set r = Range("Z1")
r.EntireColumn.Clear
r.Offset(0, 1).EntireColumn.Clear
i = 1
For j = LBound(n) To UBound(n)
r.Offset(i, 0).Value = n(j)
i = i + 1
Next
Range("Z2:Z65536").Sort Key1:=Range("Z2")
For j = 2 To Rows.Count
If IsEmpty(r.Offset(j, 0)) Then
Exit For
End If
r.Offset(j, 1).Value = r.Offset(j, 0).Value - r.Offset(j - 1, 0).Value
Next
Set rr = Range("AA:AA")
j = Application.WorksheetFunction.Min(rr)
r.Value = j
Application.EnableEvents = True
End Sub
This uses columns Z and AA as helper column aand assumes the string of
values is in cell A1.
The items are placed in Z2 on down
The differences are in AA3 on down
The minimum difference is placed in Z1
Set the conditional formatting of A1 to:
Formula Is
=Z1<3
and format as you like.
Put the code in the Worksheet code area, not a standard module.
--
Gary''s Student - gsnu200721
|