# highlight a gap in a series of numbers

#### meharper

##### New Member
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

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.

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:

Code:
``````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.

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.

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.

Replies
9
Views
219
Replies
8
Views
202
Replies
13
Views
567
Replies
0
Views
51
Replies
2
Views
356

1,218,576
Messages
6,143,317
Members
450,477
Latest member
teresab543

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back