Conditional Format - NON-numerical conditions?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 27 Jul 2006 22:05 GMT

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-

Reply to this Message

Froggatt - 28 Jul 2006 01:55 GMT

> Today I was trying to do something - I had a range which contained some
> cells containing the text string 'Manufacturer:' (my inverted commas)
[quoted text clipped - 15 lines]
>
> -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

Reply to this Message

Jay - 28 Jul 2006 19:18 GMT

>> Today I was trying to do something - I had a range which contained
>> some cells containing the text string 'Manufacturer:' (my inverted
[quoted text clipped - 30 lines]
> 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

Reply to this Message

jimdilger@gmail.com - 28 Jul 2006 11:30 GMT

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

> Today I was trying to do something - I had a range which contained some
> cells containing the text string 'Manufacturer:' (my inverted commas)
[quoted text clipped - 14 lines]
>
> -Jay-

Reply to this Message

Mac Forum / Applications / Excel / July 2006

Conditional Format - NON-numerical conditions?