Numeric Sequence Validation

[MaxPower]

I'm wondering if anyone knows a way to use Excel to validate numbers in a column for sequence.
I'm pulling data from an Access database. Each row has various data including one cell that I need to check to see if it's in sequence with the one above. Maybe shade that cell a different color.

In other words if the row above has "645" and the next row that cell has "647" turn that cell red.

Is this possible?

[Dr Stupid]

Hi Max.

I think I have an idea about what you're trying to do.
I'm jsut typing something up that may work.
If it does, I'll post it to you, if not...sorry

Write you back shortly.

[VBA Noob]

Try Conditional formatting. If you column is Col A then enter the below

=$A2>$A1

VBA Noob

[Dr Stupid]

Right! I think I have it.

I have to assume that your sequence means that each number must have a gap of three to be displayed as red, so this will do just that.

Select the second record in your row (one beneath the first).
Go to "Conditional Formatting"
Change the cell format to "Cell is:" <Equal to> <=($g3+3)>

(In my test, the second record was in cell g3)

Change the pattern of the format to Red and font color to something easy to read.

Once that is done, replicate it down the column. (Select the cell with the formatting in. click and hold the bottom right of the selection box, then drag it until you've reached the right cell.
The problem with this is that it overwrites whatever else was in the rest of the column, so that would have to be done again, but it's better than applying formatting to each cell in your sheet...which may be 500 records.


Hope that helps.

Dr Stupid

[Dr Stupid]

Try Conditional formatting. If you column is Col A then enter the below




VBA Noob

This will work better. Because it isn't clear if your sequence is linear.
Both will do a similar job though

[dominicb]

Good afternoon MaxPower

My add-in, available via the link below, has a utility that will take a number sequence from a range (it doesn't have to start at 1, and the numbers don't have to be in numeric order, but only integers) and will report either in the dialog box or pasted to a range any numbers missing from the seuqence. Once installed go to Ultimate > Range > Number Sequence Checker.

HTH

DominicB

[MaxPower]

Thanks all. I'll give these all a try. Also special thanks to dominicb for offering his add-in which provides a host of other useful, time saving functions. I plan on playing with that in my spare time.