Consecutive numbers

[Sparky]

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.

 

[wob]

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.

 

[Sparky]

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.

Thanks in advance

 

[bolo]

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

 

[Sparky]

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?

Thank for your reply

 

[Dragracer]

reread Bolo's message.

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

 

[Sparky]

Dragracer

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

 

[bolo]

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

 

[Yogi Anand]

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
	A	B	C	D
1	32		32
2	2		2
3	3		3
4	4		4
5	5		5
6	6		6
7	7		7
8	11		11
9	12		12
10	10		10
11	11		11
12	12		12
13	13		13
14	14		14
15	15		15
16	16		16
17	17		17
18	18		18
19	19		19
20	20		20
21	21		21
22	22		22
23	23		23
24	24		24
25	25		25
26	ConditionalFormat using Bolo'sFormula		ConditionalFormat using Yogi'sFormula
Sheet8

</SPAN>

Regards!

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

 

[bolo]

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