highlight a gap in a series of numbers

meharper

New Member
Joined
Aug 1, 2007
Messages
2
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

LxQ

Well-known Member
Joined
Feb 9, 2006
Messages
619
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
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.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
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

New Member
Joined
Aug 1, 2007
Messages
2
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.
 

Forum statistics

Threads
1,181,056
Messages
5,927,864
Members
436,573
Latest member
CMR237

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top