Format only cells containing certain word

[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-

[Dave Peterson]

You can use strings in Format|Conditional formatting:

With A1 the activecell
formula is:

=countif(a1,"*manufacturer:*")>0

or

formula is:
=search("manufacturer:",a1)


Jay wrote:
>
> 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-

--

Dave Peterson

[Don Guillett]

try

Sub formatifstring()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If UCase(Left(Cells(i, "c"), 3)) = "MAN" Then _
Cells(i, "c").Font.Bold = True
Next i
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"Jay" <[email protected]> wrote in message
news:[email protected]...
> 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-

[Jay]

Thanks for the suggestions. I'm assuming the asterisks work as
wildcards like in Access? So the countif fn would check for the string
anywhere within the cell?

Cheers

Jay


Dave Peterson wrote:
> You can use strings in Format|Conditional formatting:
>
> With A1 the activecell
> formula is:
>
> =countif(a1,"*manufacturer:*")>0
>
> or
>
> formula is:
> =search("manufacturer:",a1)
>
>
> Jay wrote:
>> 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-
>

[Dave Peterson]

Yep.

(Although I don't speak the access, the rest sounds very familiar <bg>.)

Jay wrote:
>
> Thanks for the suggestions. I'm assuming the asterisks work as
> wildcards like in Access? So the countif fn would check for the string
> anywhere within the cell?
>
> Cheers
>
> Jay
>
> Dave Peterson wrote:
> > You can use strings in Format|Conditional formatting:
> >
> > With A1 the activecell
> > formula is:
> >
> > =countif(a1,"*manufacturer:*")>0
> >
> > or
> >
> > formula is:
> > =search("manufacturer:",a1)
> >
> >
> > Jay wrote:
> >> 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-
> >

--

Dave Peterson