highlight a gap in a series of numbers

[meharper]

I realize this is probably an easy problem but it has me stumped and I would really appreciate any assistance with this: I have approximately 36,000 lines of 10-digit numbers that I have already sorted sequentially. However, there are random gaps here and there. Instead of looking line by line and determining where these breaks are, I was trying to figure out a way to highlight where there are gaps in the sequence. I hope I am explaining this in a way that makes sense. Any way, as you can tell, I am definately a novice. Any assistance would be appreciated.

Thanks

 

[LxQ]

I don't know how to do this faster, but the way I do it is go to the second cell and set up a conditional formatting so that it would highlight if it's not the next number from the cell above it. Then copy the formatting down.

 

[schielrn]

Welcome to the board. I'm assuming by when you say there is a gap in the sequence you mean:

1234567890
1234567892

That would be a gap. If so try this macro:

Sub test()
Dim a As Integer, b As Integer
For i = 2 To ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
If (Cells(i, 1).Value - 1 <> Cells(i - 1, 1).Value) Then
Cells(i, 1).Interior.ColorIndex = 6
End If
Next i
End Sub

This is assuming that your numbers are in column A and they start in row 1. If that is not the case therer would need to be minor changes. Also this assumes that there is no gaps in the numbers meaning no spaces.

 

[schielrn]

AhHa,

In Cell A2 if that is where the data is go to conditional formatting and input the formula:

=A2<>A1+1

Then in format put back color to whatever color you want to highlight it and then copy cell A2 and select the rest of the cells and paste special format. That should do the trick without a macro.

 

[meharper]

Thanks Everyone

Thank you so much for your great advice! I really appreciate it more than you know. You have no idea how long I have been scratching me head over that one. Have a wonderful evening.