Formuale to track numbers sequence, change the colour of the font

[nimbi]

Hi All,

1.) Is there a forumale in excel to detect and count to track a list of sequemce numbers. Eg:

00001
00002
00004
00005

No of Records not in sequence : 01


2.) Is there any formuale to change the colour of font if the font display a "WRONG" text ?


Please help
Thank you.

[NBVC]

1. Perhaps something like: =SUMPRODUCT(--(OFFSET(A1:A5,1,0)<>A1:A5+1))-1 where A1:A5 contain your record numbers...

2. Select Range and go to Format|Conditonal Formatting...

Cell Value Is >> Equal to >> ="WRONG"

Click Format and choose format from Font tab.

[nimbi]

1. Perhaps something like: =SUMPRODUCT(--(OFFSET(A1:A5,1,0)<>A1:A5+1))-1 where A1:A5 contain your record numbers...

2. Select Range and go to Format|Conditonal Formatting...

Cell Value Is >> Equal to >> ="WRONG"

Click Format and choose format from Font tab.

Hi,

I have tried the second one and it works but for the 1st one. It only check from A1:A5, how should I go about changing the forumale if I want to check from A1:A40.

Please advise.

Thanks

[NBVC]

Just change the ranges A1:A5 in the formula to the range you want, i.e. A1:A40

[nimbi]

Just change the ranges A1:A5 in the formula to the range you want, i.e. A1:A40

Hi,

I tried using your formuale but not able to get the results. Could you pls advise me on that.

Thanks

[NBVC]

Post a sample workbook.

[nimbi]

Post a sample workbook.

Hi,

Thank you very much. I am able to get it. How should I input the conditional formatting if I want to compare 02 columns value and generate "OK" for similiar value. Eg:

Col A Col B Results
1111 1111 OK

[NBVC]

You mean?

=If(A1=B1,"Ok","")