Hi!

I am using Excel 2002. I want to set up a conditional format that look for a
piece of text. Anyplace in column A (range A:A) where the word "work" in any
format (eg. WORK, Work, work, WoRk, work_, work-, etc.) is found I need the
background color changed. Other than the search function what can be used to
do this? I have tried everyway I can think of to get search to work, but
because it wants a cell reference or a piece of text I have not been able to
make it work.

This would be really easy if one of the options was "contains" rather than
expecting the values to be numbers.

I would appreciate any ideas, hints, tips, examples, etc.

Thanks for your time and help!

Dave Venus

Use a conditional formatting formula of

=ISNUMBER(SEARCH("work",A2))

starting at A" cell

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"dvenus" <(E-Mail Removed)> wrote in message
news:4DC5CDC3-6AB5-4226-88CC-(E-Mail Removed)...
> Hi!
>
> I am using Excel 2002. I want to set up a conditional format that look for
> a
> piece of text. Anyplace in column A (range A:A) where the word "work" in
> any
> format (eg. WORK, Work, work, WoRk, work_, work-, etc.) is found I need
> the
> background color changed. Other than the search function what can be used
> to
> do this? I have tried everyway I can think of to get search to work, but
> because it wants a cell reference or a piece of text I have not been able
> to
> make it work.
>
> This would be really easy if one of the options was "contains" rather than
> expecting the values to be numbers.
>
> I would appreciate any ideas, hints, tips, examples, etc.
>
> Thanks for your time and help!
>
> Dave Venus

Highlight column A, with A1 as the active cell, then click on Format |
Conditional Formatting, and in the panel that pops up select Formula
Is rather than Cell Value Is and in the next panel enter this formula:

=ISNUMBER(SEARCH("work",A1))

then click the Format button, select the Patterns tab (for background
colour), and select your colour. OK your way out.

Hope this helps.

Pete

On Jan 31, 5:04*pm, dvenus <dve...@discussions.microsoft.com> wrote:
> Hi!
>
> I am using Excel 2002. I want to set up a conditional format that look fora
> piece of text. Anyplace in column A (range A:A) where the word "work" in any
> format (eg. WORK, Work, work, WoRk, work_, work-, etc.) is found I need the
> background color changed. Other than the search function what can be used to
> do this? I have tried everyway I can think of to get search to work, but
> because it wants a cell reference or a piece of text I have not been able to
> make it work.
>
> This would be really easy if one of the options was "contains" rather than
> expecting the values to be numbers.
>
> I would appreciate any ideas, hints, tips, examples, etc.
>
> Thanks for your time and help!
>
> Dave Venus

Another way is to use =countif()

=countif(a:a,"*work*")>0

dvenus wrote:
>
> Hi!
>
> I am using Excel 2002. I want to set up a conditional format that look for a
> piece of text. Anyplace in column A (range A:A) where the word "work" in any
> format (eg. WORK, Work, work, WoRk, work_, work-, etc.) is found I need the
> background color changed. Other than the search function what can be used to
> do this? I have tried everyway I can think of to get search to work, but
> because it wants a cell reference or a piece of text I have not been able to
> make it work.
>
> This would be really easy if one of the options was "contains" rather than
> expecting the values to be numbers.
>
> I would appreciate any ideas, hints, tips, examples, etc.
>
> Thanks for your time and help!
>
> Dave Venus

--

Dave Peterson

I misread the range.

You could use:
=countif(a1,"*work*")>0
to test any one particular cell.

=countif(a:a,"*work*")>0
will look for work in any cell in column A.

dvenus wrote:
>
> Hi!
>
> I am using Excel 2002. I want to set up a conditional format that look for a
> piece of text. Anyplace in column A (range A:A) where the word "work" in any
> format (eg. WORK, Work, work, WoRk, work_, work-, etc.) is found I need the
> background color changed. Other than the search function what can be used to
> do this? I have tried everyway I can think of to get search to work, but
> because it wants a cell reference or a piece of text I have not been able to
> make it work.
>
> This would be really easy if one of the options was "contains" rather than
> expecting the values to be numbers.
>
> I would appreciate any ideas, hints, tips, examples, etc.
>
> Thanks for your time and help!
>
> Dave Venus

--

Dave Peterson

Hi!

Thanks for both answers! And so quickly! You guys are awesome! Worked like a
champ!

Now where is that explained in the manual? I don't think I would have
ever found that.

Thanks!

Dave Venus

I'm using Excel 2003 with Excel SP2 and I've tried all of the
suggestions in this thread... all without any success. I'm doing an
exact copy and paste but using my own search word in place of 'work'.
Each time I get an error message saying 'the formular you typed
contains an error'.

I think it's time to share the details.

What cell was active when you tried the format|Conditional formatting?
What was the formula that failed?



(E-Mail Removed) wrote:
>
> I'm using Excel 2003 with Excel SP2 and I've tried all of the
> suggestions in this thread... all without any success. I'm doing an
> exact copy and paste but using my own search word in place of 'work'.
> Each time I get an error message saying 'the formular you typed
> contains an error'.

--

Dave Peterson

This is what I've tried most recently:

=ISNUMBER(SEARCH("catalogue*",AH115))

I tried it just on cell AH115 (just the one cell selected) and also on
the entire column (column AH).

Nobody suggested using a *

=ISNUMBER(SEARCH("catalogue",AH115))

but I doubt that is the problem. Although your email domain is uk are you
using a continental setting? Maybe try


=ISNUMBER(SEARCH("catalogue";AH115))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



<(E-Mail Removed)> wrote in message
news:9673a7fc-625a-4648-be9e-(E-Mail Removed)...
> This is what I've tried most recently:
>
> =ISNUMBER(SEARCH("catalogue*",AH115))
>
> I tried it just on cell AH115 (just the one cell selected) and also on
> the entire column (column AH).
>