Partial match of first N characters conditional formating

Hi,

i am new here. I am trying to do simple matcher in excel. I need to compare first 11 characters in A1 cell with first 11 characters in A2:A1000 cells. If the first 11 characters are eqals, it change color to green, else it will be red. In newer version of excel (2013/2016) i simply used format only cell that contains specific text begins with formula: =LEFT($A$1;11). The problem is, 2007 version does not support using formula in this specific conditional formating. What i am trying to do is finding solution for 2007 version. So far i tried to use 2nd column. A column is for value only. B column is, where i try to use adjusted conditional formating.
I used this formula found on another thread:

=IF(A1="","",IF(ISNA(MATCH(LEFT($A$1,11)&"*",A1:$A$1000,0)),"NG","OK"))

it is working well, but only until one special condition happen. I try to explain on example

81740-G40002018051500001 OK
81740-G40002018051500005 OK
81740-G42002018051500003 NG


These are examples of input in column A. Everything is working. If match - OK, if not match NG. But when i put 4th value that is OK (81740-G40002018051500004 for example) after previous value that was NG it automatically changes all my values in Column B to OK, even when it does not match.

Please be careful with version. I need to run it on Office 2007 version.

Does not work well. I was able to use it but it is not working well. When i use it, it looks somethink like this

81740-G40002018051500001
81740-G40002018051500002
81740-G40002018051500003
81740-G42002018051500004
81740-G42002018051500005
81740-G40002018051500006

It will change color of previous cell, not the cell where i put new data. Also it wil change previous cell that should be wrong to green after i input value that match the formula condition.

Yes my data starts in A1. I found a solution. The formula does not work properly, if you comparing data with value in same column. So instead i am using formula with conditional formating on column A and comparing to value in cell B1. I just simply use trick B1=A1 and now it works as i wanted. BTW this issue happened only on 2007 version or maybe lower. 2013/2016 works as it should. You can mark this thread as solved.

i think FDibbins mean is
Did you either highlight/apply to A2:A1000 or A:A?

if you apply to A:A,
the formula should be below instead

Formula:

Please Login or Register  to view this content.

if you apply to A2:A100
you may follow the FDibbins formula.

finally, you can mark thread solved through
Thread Tool > Mark thread solved.

Originally Posted by BoredWorker

i think FDibbins mean is
Did you either highlight/apply to A2:A1000 or A:A?

if you apply to A:A,
the formula should be below instead

Formula:

Please Login or Register  to view this content.

if you apply to A2:A100
you may follow the FDibbins formula.

finally, you can mark thread solved through
Thread Tool > Mark thread solved.

Thanks, your formula worked. I applied to A:A.
Now i can mark this thread solved.