Conditional Format?

[Guest]

Excel2003 ...

Col C ... contains <=5 char text starting with 0 to 9
Col D ... contains <=8 char text starting with 0 to 9

Sometimes ... the next to last char in Col D is a "P" or a "T" ... whenever
this occurs the last Char of Col D must match 1st char in Col C ... I would
like to conditional format where this does not happen.

ie:

Col C = 3052 ... Col D = 123456P3 ... = valid value (3) ... no format
Col C = 3052 ... Col D = 123456T3 ... = valid value (3) ... no format
Col C = 4052 ... Col D = 123456T1 ... = invalid value (4 vs 1) ... format
Col C = 4052 ... Col D = 123456P3 ... = invalid value (4 vs 3) ... format
Col C = 4052 ... Col D = 123456P4 ... = valid value (4) ... no format
Col C = 3317 ... Col D = 3317-123 .... = (non issue ... no "P" or "T")

Thanks ... Kha

 

[Bob Phillips]

Use a formula of

=AND(OR(MID(D1,LEN(D1)-1,1)="P",MID(D1,LEN(D1)-1,1)="T"),RIGHT(D1,1)<>LEFT(C1))

in the CF

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Awesome ... works great ... Thanks ... Kha

Use a formula of

=AND(OR(MID(D1,LEN(D1)-1,1)="P",MID(D1,LEN(D1)-1,1)="T"),RIGHT(D1,1)<>LEFT(C1))

in the CF

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)