 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 20082002: Conditional formatting question
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  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi! > [quoted text clipped - 19 lines] > > Dave Venus 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 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 > Hi! > [quoted text clipped - 14 lines] > > Dave Venus Another way is to use =countif() =countif(a:a,"*work*")>0 > Hi! > [quoted text clipped - 14 lines] > > Dave Venus SignatureDave Peterson 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? > 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'. SignatureDave 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)) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > 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). 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! > [quoted text clipped - 14 lines] > > Dave Venus SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.