Conditional Format - NON-numerical conditions?

[Jay]

Today I was trying to do something - I had a range which contained some
cells containing the text string 'Manufacturer:' (my inverted commas)
and wanted to format only those cells.

Now, I couldn't autofilter, advanced filter or sort because of the sheet
structure, and amending it would have taken as long as manually
formatting the relevant cells. So I was thinking of how to do it. I was
thinking of a conditional format of any cells containing the string.
But non-numerical (or non-formula) conditions don't appear to be possible.

Could anyone tell me if and how it is possible. Or, alternatively,
another way to format cells within a range containing a specified text
string? (Although they contain the word 'Manufacturer:' along with the
manufacturer name which changes from cell to cell.)

Any help would be greatly appreciated.

-Jay-

 

[Froggatt]

Today I was trying to do something - I had a range which contained some
cells containing the text string 'Manufacturer:' (my inverted commas)
and wanted to format only those cells.

Now, I couldn't autofilter, advanced filter or sort because of the
sheet structure, and amending it would have taken as long as manually
formatting the relevant cells. So I was thinking of how to do it. I
was thinking of a conditional format of any cells containing the
string. But non-numerical (or non-formula) conditions don't appear to
be possible.

Could anyone tell me if and how it is possible. Or, alternatively,
another way to format cells within a range containing a specified text
string? (Although they contain the word 'Manufacturer:' along with the
manufacturer name which changes from cell to cell.)

Any help would be greatly appreciated.

-Jay-

I've just had a go and this seems to work:
Highlight your range, and let's assume that the top left cell is A9.

Once highlighted, select Conditional Formatting... and choose Formula Is
Then type

=LEFT(A9,12)="manufacturer" and select your chosen format pattern.

This looks to see if the first 12 letters in any of the highlighted
cells spell "manufacturer", with any capitalisation variations. If so,
then your chosen format is applied.

Hope this is what you wanted.
Mr F

 

[jimdilger]

Jay,

Mr F's post introduced me to using a formula in conditional format.
Here is a variation that may be a tad better.

=FIND("manufacturer",A1,1)>0

now as long as 'manufacturer' appears somewhere in A1, you will get
your formatting.

Jim

 

[Jay]

I've just had a go and this seems to work:
Highlight your range, and let's assume that the top left cell is A9.

Once highlighted, select Conditional Formatting... and choose Formula Is
Then type

=LEFT(A9,12)="manufacturer" and select your chosen format pattern.

This looks to see if the first 12 letters in any of the highlighted
cells spell "manufacturer", with any capitalisation variations. If so,
then your chosen format is applied.

Hope this is what you wanted.
Mr F

That's perfect. It's also opened up a plethora of ideas - now I see how
formulas can be conditionally formatted.

Cheers Mr F

Jay