Consecutive numbers

Sparky

Board Regular
Joined
Feb 18, 2002
Messages
210
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

wob

Board Regular
Joined
May 21, 2002
Messages
105
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

Board Regular
Joined
Feb 18, 2002
Messages
210
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Mar 23, 2002
Messages
423
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

Board Regular
Joined
Feb 18, 2002
Messages
210
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jun 9, 2002
Messages
151
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

Board Regular
Joined
Feb 18, 2002
Messages
210
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Dragracer

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

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
Fair play. i could see that error but i wasn't sure how to get round it. Nice one.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

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