PC Review


Reply
Thread Tools Rate Thread

2002: Conditional formatting question

 
 
dvenus
Guest
Posts: n/a
 
      31st Jan 2008
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
 
Reply With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      31st Jan 2008
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



 
Reply With Quote
 
Pete_UK
Guest
Posts: n/a
 
      31st Jan 2008
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


 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      31st Jan 2008
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
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      31st Jan 2008
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
 
Reply With Quote
 
dvenus
Guest
Posts: n/a
 
      31st Jan 2008
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


 
Reply With Quote
 
ideas@zestylemon.co.uk
Guest
Posts: n/a
 
      3rd Feb 2008
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'.
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      3rd Feb 2008
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
 
Reply With Quote
 
ideas@zestylemon.co.uk
Guest
Posts: n/a
 
      3rd Feb 2008
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).

 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      3rd Feb 2008
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).
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting (Excel 2002 SP3) =?Utf-8?B?dmVydGlnbw==?= Microsoft Excel Worksheet Functions 5 20th Apr 2007 10:24 AM
Conditional formatting.... Excel 2002 Gunjani Microsoft Excel Discussion 1 13th Feb 2006 10:58 AM
Conditional formatting.... Excel 2002 Gunjani Microsoft Excel Worksheet Functions 1 13th Feb 2006 10:58 AM
Excel Conditional Formatting Error in 2003 and 2002 =?Utf-8?B?S01I?= Microsoft Excel Crashes 0 6th Feb 2006 10:08 PM
Conditional Formatting-Access 2002 gail Microsoft Access Form Coding 2 23rd Apr 2004 01:35 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:31 PM.