Conditional Formatting override

[thegrump179]

I am trying to come up with a way of overriding a conditional Format in column F.
I am using =NOT(ISERR(SEARCH("TP",E1))) to highlight column F, if the figure in column E starts with TP ie TP25478. The promt is to remind me to put a number in column F. So if I put a number into the highlighted Cell (in F) I want the highlight to be removed.
There are other prefexes such as LP SP RP that I could also use but TP is the main problem because 99% of the time it is number 1 and I keep forgetting.

Thanks Ross

 

[GlennUK]

Maybe:

=OR(NOT(ISERR(SEARCH("TP",E1))),F1 <> "")

?

 

[thegrump179]

Sorry Glen. It doesn't seem to work. It highlights the heading in F1, no mater where in column E you put the TP

Regards Ross

 

[GlennUK]

Sorry, I got my logic the wrong way round. Try this:

=AND(NOT(ISERR(SEARCH("TP",E1))),F1="")

 

[thegrump179]

works a treat. Thanks Glen

 

[GlennUK]

Oh good, I am glad about that.

 

[Jonmo1]

Glad the OP got the issue sorted...

Just a tip...

Most of the time, you don't need to test for errors in conditional formatting.
Because CF considers errors as FALSE.
Or more accurately, it considers errors NOT TRUE..
Also, search returns a number greater than 0 when the string is found.
Any # Greater than 0 is considered TRUE by conditional formatting.

So you can remove the Not(Iserr, and just use Search by itself.

=AND(SEARCH("TP",E1),F1="")


Hope that helps.