Conditional Formatting - highlight row based on cell not containing text

[MrChud]

I hope someone can help me as I'm getting a real headache from this one!

I have a report from a supplier that contains branch codes - I want to apply a few rules to highlight (grey-out) branches that aren't relevant; all the branches we are interested in start with Q94 followed by another 4 or 5 numbers.

The branch code is in column A, data starts on row 5

I've tried using (under formula option in CF):

=$A5<>"Q94*" - This seems to work until you extend the 'applies to' to rows below which it also highlights despite them containing a branch code starting with Q94

=SEARCH("Q94*",$A5)>0 - Works to find and highlight Q94 codes but when changed to =SEARCH("Q94*",$A5)<1 it doesn't work either??

Any clues?

 

[Marcelo Branco]

Maybe...

=LEFT($A5,3)<>"Q94"

Hope this helps

M.

 

[njimack]

Try one of:
=ISNUMBER(SEARCH("Q94",$A5))
=ISERROR(SEARCH("Q94",$A5))