Multiple criteria validation in multiple cells

Hi Everyone,

I've got multiple cells with strings of text that look like the following:

07000178CA39DN8000000000 20150111 050099999999
07000178CA39EN5600000000 20150111 020099999999
07000357CA39DN8000000000 20151115 000099999999
07000357CA39EN5600000000 20151115 000099999999


The first 8 characters of the string correspond to an employee, and there are multiple lines for every single employee. Keeping that in mind, for every different employee in the list, I'm trying to validate that the following:

1. Check the string that contains "DN80" as characters 13 to 16 for the value contained in characters 35 and 36
2. If the value of characters 35 and 36 is higher than "04", and this is where it gets tricky, I need to go check in another line (that contains "EN56" as chr 13 to 16), but for the same employee (8 first characters), if the value of chr 35 and 36 is "02". If it's not, I need to turn it in red font.

My main issue here is figuring out how to validate values in a different cell than the one with the first criteria, while remaining in the same employee (8 first digits).

Do you think it can be done?

Thanks a lot!

sheet is selected and the data is in column A
this can do

Please Login or Register  to view this content.

Kind regards
Leo

Thanks for this, it works but I still get a Type Mismatch Error on the following line:

If Val(Mid(Range("A" & r), 35, 2)) <> 2 Then

Any Idea why??

can you poste the file ?


Kind regards
Leo