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 | |
---|
|
---|
</SPAN>
Regards!
Yogi
This message was edited by Yogi Anand on 2002-10-11 14:36