2002: Conditional formatting question

  • Thread starter Thread starter dvenus
  • Start date Start date
D

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
 
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)
 
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
 
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.
 
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?
 
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)
 
Back
Top