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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Dragracer

I've got it to work. Many thanks especially to Bolo.......keep up the good work.
 
Upvote 0
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
 
Upvote 0
Fair play. i could see that error but i wasn't sure how to get round it. Nice one.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top