# Consecutive numbers

#### Sparky

##### Board Regular
I have a column with 25 consecutive numbers, example 1 to 25. How do I highlight a cell or cells if someone changes one or more of the numbers in the column and breaks the succession? Example, if the numbers 1 to 25 are in cells A1:A25 and someone types the number 32 in A1 I need A1 to be highlighted as in conditional formatting.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
hard one this.

i. inserting cells can screw any
'add one to the cells above the one' formulas, as can cut and paste.

ii. picking the first value, dragging down with the RIGHT mouse button will provide a pop up menu, choose the option that's best.

There is no formula where 1 is added to the cell above. I just filled the column with 25 numbers using the bottom right hand corner of the cell and dragging down. I tried what you suggested, it is not what I require. If a number is changed (or is not a consecutive number) the cell or cells need to be highlighted.

Try this in the conditional formatting

select A2 and then CF. Select cell formula and put:

=and((\$A3-\$A2<>1),(\$A2-\$A1<>1))

This will change the colour of any number that is not in sequence. Hope this is what you want.
This message was edited by bolo on 2002-10-10 21:13

bolo

I tried your formula and assuming A1:A25 house the numbers 1 to 25.
A25 is always red and if for example I enter 33 in A12, A11 turns red.
Any further suggestions?

Start your Conditional formating in "A2".

You will have to use a coditional format for A1. Just make it not equal to 1

easy mistake

Dragracer

I've got it to work. Many thanks especially to Bolo.......keep up the good work.

Glad you got it to work. Sorry i wasn't too clear!!!

On 2002-10-10 19:44, Sparky wrote:
I have a column with 25 consecutive numbers, example 1 to 25. How do I highlight a cell or cells if someone changes one or more of the numbers in the column and breaks the succession? Example, if the numbers 1 to 25 are in cells A1:A25 and someone types the number 32 in A1 I need A1 to be highlighted as in conditional formatting.

Hi Sparky, Bolo, and Dragracer:

Bolo's formulation will corectly highlite if an errant entry 32 were to be placed in cell A1, but will not pick up errant entries if they are sequential numbers (e.g. 11 and 12 where 8 and 9 should have been). Let me show that by simulation comparing Bolo's and my suggested formulation

For my suggested formulation, let us say your numbers are in cells C1 through C25.
Select cell C1 then CF ... then Formula Is

=C1<>ROW(C1) -- then Format|Patterns|Color|Red

Please see the worksheet simulation, where I depict, Bolo's and Yogi's CF formulations side by side
y021010h1.xls
ABCD
13232
222
333
444
555
666
777
81111
91212
101010
111111
121212
131313
141414
151515
161616
171717
181818
191919
202020
212121
222222
232323
242424
252525
26ConditionalFormat using Bolo'sFormulaConditionalFormat using Yogi'sFormula
Sheet8
</SPAN>

Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-11 14:36

Fair play. i could see that error but i wasn't sure how to get round it. Nice one.

Replies
1
Views
140
Replies
2
Views
352
Replies
0
Views
113
Replies
3
Views
194
Replies
0
Views
126

1,203,094
Messages
6,053,507
Members
444,667
Latest member
KWR21

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