change in message sequence

[Chris1981]

If I have a long list of numbers, how do I find where that list of numbers is not consecutive. How do I show when the sequence changes from 5 to 8 and from 9 to12?

1
2
3
4
5
8
9
12

 

[Rick Rothstein]

Assuming your numbers start in cell A1, put this formula in cell B2 and copy it down to the bottom of your data...

=IF(A2-A1>1,"<==","")

 

[Trebor76]

Here's a simple macro that will highlight the relevant cell(s) in yellow:

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False

    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    'Works backwards from the last Row in Col. A to Row 2. Change to suit.
    For lngMyRow = lngLastRow To 2 Step -1
        If Val(Range("A" & lngMyRow)) - 1 <> Val(Range("A" & lngMyRow - 1)) Then
            Range("A" & lngMyRow).Interior.Color = RGB(255, 255, 0)
        Else
            Range("A" & lngMyRow).Interior.Color = xlNone
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Robert