2002: Conditional formatting question

[dvenus]

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

 

[Bob Phillips]

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)

 

[Pete_UK]

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

 

[Dave Peterson]

Another way is to use =countif()

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

 

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

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

 

[Dave Peterson]

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?

 

[Bob Phillips]

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)