Format only cells containing certain word

Today I was trying to do something - I had a range which contained some 
cells containing the text string 'Manufacturer:'  (my inverted commas) 
and wanted to format only those cells.

Now, I couldn't autofilter, advanced filter or sort because of the sheet 
structure, and amending it would have taken as long as manually 
formatting the relevant cells.  So I was thinking of how to do it. I was 
thinking of a conditional format of any cells containing the string. But 
non-numerical (or non-formula) conditions don't appear to be possible.

Could anyone tell me if and how it is possible.  Or, alternatively, 
another way to format cells within a range containing a specified text 
string? (Although they contain the word 'Manufacturer:' along with the 
manufacturer name which changes from cell to cell.)

Any help would be greatly appreciated.

-Jay-
0
zeugma (59)
7/27/2006 9:20:19 PM
excel 39879 articles. 2 followers. Follow

4 Replies
319 Views

Similar Articles

[PageSpeed] 21

You can use strings in Format|Conditional formatting:

With A1 the activecell
formula is:

=countif(a1,"*manufacturer:*")>0

or

formula is:
=search("manufacturer:",a1)


Jay wrote:
> 
> Today I was trying to do something - I had a range which contained some
> cells containing the text string 'Manufacturer:'  (my inverted commas)
> and wanted to format only those cells.
> 
> Now, I couldn't autofilter, advanced filter or sort because of the sheet
> structure, and amending it would have taken as long as manually
> formatting the relevant cells.  So I was thinking of how to do it. I was
> thinking of a conditional format of any cells containing the string. But
> non-numerical (or non-formula) conditions don't appear to be possible.
> 
> Could anyone tell me if and how it is possible.  Or, alternatively,
> another way to format cells within a range containing a specified text
> string? (Although they contain the word 'Manufacturer:' along with the
> manufacturer name which changes from cell to cell.)
> 
> Any help would be greatly appreciated.
> 
> -Jay-

-- 

Dave Peterson
0
petersod (12005)
7/27/2006 9:30:47 PM
try

Sub formatifstring()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If UCase(Left(Cells(i, "c"), 3)) = "MAN" Then _
Cells(i, "c").Font.Bold = True
Next i
End Sub

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Jay" <zeugma@toucanspam.com> wrote in message 
news:uDP$BKcsGHA.4596@TK2MSFTNGP04.phx.gbl...
> Today I was trying to do something - I had a range which contained some 
> cells containing the text string 'Manufacturer:'  (my inverted commas) and 
> wanted to format only those cells.
>
> Now, I couldn't autofilter, advanced filter or sort because of the sheet 
> structure, and amending it would have taken as long as manually formatting 
> the relevant cells.  So I was thinking of how to do it. I was thinking of 
> a conditional format of any cells containing the string. But non-numerical 
> (or non-formula) conditions don't appear to be possible.
>
> Could anyone tell me if and how it is possible.  Or, alternatively, 
> another way to format cells within a range containing a specified text 
> string? (Although they contain the word 'Manufacturer:' along with the 
> manufacturer name which changes from cell to cell.)
>
> Any help would be greatly appreciated.
>
> -Jay- 


0
dguillett1 (2487)
7/27/2006 9:33:35 PM
Thanks for the suggestions.  I'm assuming the asterisks work as 
wildcards like in Access? So the countif fn would check for the string 
anywhere within the cell?

Cheers

Jay


Dave Peterson wrote:
> You can use strings in Format|Conditional formatting:
> 
> With A1 the activecell
> formula is:
> 
> =countif(a1,"*manufacturer:*")>0
> 
> or
> 
> formula is:
> =search("manufacturer:",a1)
> 
> 
> Jay wrote:
>> Today I was trying to do something - I had a range which contained some
>> cells containing the text string 'Manufacturer:'  (my inverted commas)
>> and wanted to format only those cells.
>>
>> Now, I couldn't autofilter, advanced filter or sort because of the sheet
>> structure, and amending it would have taken as long as manually
>> formatting the relevant cells.  So I was thinking of how to do it. I was
>> thinking of a conditional format of any cells containing the string. But
>> non-numerical (or non-formula) conditions don't appear to be possible.
>>
>> Could anyone tell me if and how it is possible.  Or, alternatively,
>> another way to format cells within a range containing a specified text
>> string? (Although they contain the word 'Manufacturer:' along with the
>> manufacturer name which changes from cell to cell.)
>>
>> Any help would be greatly appreciated.
>>
>> -Jay-
> 
0
zeugma (59)
7/29/2006 1:53:04 PM
Yep.

(Although I don't speak the access, the rest sounds very familiar <bg>.)

Jay wrote:
> 
> Thanks for the suggestions.  I'm assuming the asterisks work as
> wildcards like in Access? So the countif fn would check for the string
> anywhere within the cell?
> 
> Cheers
> 
> Jay
> 
> Dave Peterson wrote:
> > You can use strings in Format|Conditional formatting:
> >
> > With A1 the activecell
> > formula is:
> >
> > =countif(a1,"*manufacturer:*")>0
> >
> > or
> >
> > formula is:
> > =search("manufacturer:",a1)
> >
> >
> > Jay wrote:
> >> Today I was trying to do something - I had a range which contained some
> >> cells containing the text string 'Manufacturer:'  (my inverted commas)
> >> and wanted to format only those cells.
> >>
> >> Now, I couldn't autofilter, advanced filter or sort because of the sheet
> >> structure, and amending it would have taken as long as manually
> >> formatting the relevant cells.  So I was thinking of how to do it. I was
> >> thinking of a conditional format of any cells containing the string. But
> >> non-numerical (or non-formula) conditions don't appear to be possible.
> >>
> >> Could anyone tell me if and how it is possible.  Or, alternatively,
> >> another way to format cells within a range containing a specified text
> >> string? (Although they contain the word 'Manufacturer:' along with the
> >> manufacturer name which changes from cell to cell.)
> >>
> >> Any help would be greatly appreciated.
> >>
> >> -Jay-
> >

-- 

Dave Peterson
0
petersod (12005)
7/29/2006 2:07:51 PM
Reply:

Similar Artilces:

When I am in one cell highlight another
Hello, When I am in one cell highlight another. Lets say that if my cursor is in A1 I want D1 highlighted or with another cursor on it. Then if I move to A2, D2 should be the one with another cursor or highlighted. Thank you for your help, Jose Juan Diaz hi, Jose Juan ! > When I am in one cell highlight another > ... if my cursor is in A1... D1 highlighted or with another cursor on it. > ... if I move to A2, D2 should be the one with another cursor or highlighted. 'put' a cursor on non-active-cell... [I don't think it's possible] :( to highlight 'D' wh...

Can't track certain Outlook Contacts
Hi, For one of my users (crm 3.0 - Outlook 2003), the 'Track in CRM' button does not appear for certain contacts. About 2/3s of the contacts are fine. I thought it might be contacts created in the Blackberry then sync'd to Outlook but that doesn't appear to be the case. I'm still working on finding a pattern, unfortunately this user is travelling a lot so it's hard for me to sit down with her and see what she's doing. I thought maybe someone may have had a similar experience. ...

Excel Regional Date Format Options
A client of ours in NZ is complaining that date format options for English (New Zealand) have changed from older versions of excel (they are using 2003) Some of their spreadsheets have dates formatted as dd-mmm-yy, mmm-yy and dddd,dd,mmm but these options do not exist anymore. Is there anyway to add options to this list without using the custom format option? Thanks, Jesse I just compared the Excel 97 and Excel 2003 built-in date formats and they are mostly unchanged. 2003 has a few more but I don't think there were any subtractions. The formats dd-mmm-yy and mmm-yy are righ...

Cells print so small I cannot read numbers. How do I fix?
I have been working with page break. Now I have the grid on 1 page..but it is far to small to read. now when I try to spread it back to 2 pages, it just takes the same tiny microscopic type and spreads it into 2 pages. I am stuck printing tiny type. How can I get the grid cells back to a size that is readable. It sound like you have selected Fit to 1 page in File > Page setup > Page > Scaling. Either select to fit it to 2 pages or select Adjust to 100% size -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Repl...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

word count
my word count says 1 no matter how many words I type Please help Gary Does this happen with a particular document, only, or with any document that you open in Word? -- Stefan Blom Microsoft Word MVP "raider23" wrote: > my word count says 1 no matter how many words I type > Please help > Gary ...

think cell program
is anyone familiar with a program called think cell? Any thoughts? (powerpoint v 2003). Is this an add-in? thanks Sara It's very good. You can "try for free" from their website: http://www.think-cell.com/ Recent interview with one of the founders on Indezine: http://blog.indezine.com/2009/12/think-cell-conversation-with-markus.html -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/3...

Windows XP: Open file in read-only: Word, Excel, Visio
I'm often sharing files on a network drive with someone. Often, one of us knows that we don't need to open a file for modification, and we only need read-access. Is there a way to open a file in read-only mode so that when someone does need to open it for modification, the read-only user is not blocking that? If there is not a way to do this native to Windows XP, is there a way to do it from the three applications that we use most, namely Word, Excel, Visio? All are 2003 versions. Thx. I used to save files like this in "read only recommended" mode. File|SaveAs|Tools|Ge...

Newbie Cell Reference Question...
Hello, I apologize if this question has been answered before, but I'm no quite sure what to search for as I'm not very good with excel. I'm making a spreadsheet where one sheet references another. I'll tr to explain this as best as possible.... I'm creating a spreadsheet for a fantasy basketball league. I have "Data" sheet that contains data for all players. I have another sheet "Teams" that has all the players on each team. Column B contains th players name, and column C contains a number that corresponds to th row this player is on in the dat...

Open web page from Word 2007
I have the following code to open a web page: Dim IE Set IE = CreateObject("internetexplorer.application") mySTR = "http://MyWebPage" IE.Navigate mySTR The code works if I run it from vba - it opens the page on top of Word. But if I assign it to a QAT or Ribbon button, it opens the page but leaves it minimized. How can I get it to open on top of Word? ...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

How to get rid of a "line" in Word
If I type a number of dashes in Word and press Return it automatically gets changed into a continuous line. How do I later remove such a line? Sometimes if I delete the text line above it then the line also goes but often I just can't get rid of it. I have to resort to creating a new document and then copy/paste the god bits over and then delete the old document. Thanks. -- Les Desser (The Reply-to address IS valid) In news:lzqSlgmVscxLFAoG@dessergr0up.invalid, Les Desser <NewsDump1@dessergroup.com> typed: > If I type a number of dashes in Word and press ...

VFW, webcam, image format
Hello, I'm trying to grab pictures from a webcam with Video For Windows (VFW). The source code I use is the following: HWND _capwnd = capCreateCaptureWindow("Capture",WS_POPUP,0,0,1,1,0,0); capDriverConnect(_capwnd,0); capGrabFrame(_capwnd); capEditCopy(_capwnd); OpenClipboard(NULL); HBITMAP hBmp = (HBITMAP)GetClipboardData(CF_BITMAP); CloseClipboard(); BITMAP bmp; GetObject(hBmp, sizeof(BITMAP), &bmp); int size = bmp.bmWidth*bmp.bmHeight*bmp.bmWidthBytes; unsigned char* data = new unsigned char[size]; GetBitmapBits(hBmp,size, data); ...

Cell background shading in Excel
Why is it that the background shading colors available under the Paintbucket Icon chjange from file to file? Also, how can I add some of the colors to the paintbucket that are availabe under the format/cells/pattern pulldown? Colors are a Workbook-level property, so you can change the color palette for any workbook. Choose Tools/Options/Colors... and modify the colors to your hearts' content. You can also import another workbook's color palette from that dialog. Note however, that there are only 56 bins in the color palette, so you can only use 56 colors at a time. The paint b...

Sum every other cell?
I own a used-book shop. I have an Excel SS to track how many books pe day I sell in each of 28 categories and the $$$ I take in for eac category. So there's two columns for each day: #books & $$$. therefore want to add every odd-numbered cell in a row to get the tota number of books for a category in a given period of days and ever even-numbered cell in the same row for the the total $$$. Is there simple formula or function for this -- bookmanj ----------------------------------------------------------------------- bookmanjb's Profile: http://www.excelforum.com/member.php?acti...

How do I populate the "Find" dialogue in Word with the contents of the clipboard?
Hi I tried, and failed to create a macro. (I do a procedure hundreds of times every day so wanted to automate it) I press Control-F (to open the 'find' dialogue) and then press Control- V (to paste the contents) into the dialogue. As you can see from the code below, when I recorded the macro, it used '2022' (which is what happens to be in my clipboard right now) but I want it to use the clipboard. Is this possible? I hope I've explained the problem properly. ==================================================== Sub FindWhatsinClipboar...

Slow Open of WORD
I am running MS Office XP Pro under Windows XP Pro. After downloading a Word file from the Internet and openning it with Word, Word began openning very slowly and giving the message "requesting virus scan ..." at the bottom of the Word screen. Excel started doing the same thing. All other programs on my computer continued to open normally. I run Norton Systemworks with Antivirus. I also began to notice that Windows would boot slowly and Systemworks would also open slowly. I ran a virus scan (with latest definitions) on my computer and no files were infected. After r...

How do open a scanned doc with Word?
The scanned doc shows up when I launch My Pictures or when I am attaching a doc to send in an email with Outlook, but when I jsut launch word and go to the folder the scanned docs, whether JPG's or PDF's, do not appear. Thanks. You'll need OCR software so that you can convert the document into a format that Word can read. You may find such software on the disc that came with your scanner. -- Stefan Blom Microsoft Word MVP "vwj" <vwj@discussions.microsoft.com> wrote in message news:ED74F5BD-3241-411D-AF93-1846E14BABC7@microsoft.com... > ...

MM from Word in HTML Format
I am using MS Office 2002. When I do a mail-merge from MS Word to email, it works fine as long as I use 'Plain Text' as the format. It accesses Outlook and sends the messages. However, if I use HTML format, it acts as though it is sending something, but the messages never get sent. Does anyone know what my problem might be? Brian, I seem to have the same problem in Offic 2003. When I do an email merge from Word, the screen flickers at the end but there is no record of the emails having been sent in Outlook, i.e., they are not in the Sent folder. I've tried looking around...

MERGE CELLS
I have Name, PO Box, street address, city, state, zip across a row in 6 seperate cells/columns. I want to have this format in 1 cell: Name PO Box Street address City, State Zip STEVE wrote: > I have Name, PO Box, street address, city, state, zip across a row in 6 > seperate cells/columns. > > I want to have this format in 1 cell: > > Name > PO Box > Street address > City, State Zip > You do realize that this will screw up your ability to sort the data. A better solution would be to describe what you want to do with the data. You may not need it in the ...

How do I format a cell to auto date
I would like to have a date automatically enter itself each time I open the spreadsheet. Is there a way to do this? Any help would be appreciated. Thanks Hi George Use a function like this one for todays date =TODAY() -- Regards Ron de Bruin http://www.rondebruin.nl "George" <George@discussions.microsoft.com> wrote in message news:7EF21130-EBD9-41C2-8CB5-5723BE40CBB1@microsoft.com... >I would like to have a date automatically enter itself each time I open the > spreadsheet. Is there a way to do this? Any help would be appreciated. > Thanks ...

Remove Data Format Change
I have a program that puts a database's data into an excel spreadsheet. The problem is that Excel is constantly changing the numbers to dates, which corrupts the data. I know I can switch the column data type to Text and that will fix it, but the problem is that when the data is written to the spreadsheet it overwrites that column data type and Excel does the formatting that changes my data. How can I turn this off so Excel stops trying to recognize data types and change their format? Please help because this is causing major problems for us. Thanks, ACFalcon >>I have a ...

How can I add AMA format to Word 2007
I have a few papers to write this semester for college, and all three require the usage of AMA format for citations. Word 2007 does not have AMA as an option though. Has Microsoft found or created an add-in for the AMA format yet? It is frustrating to see formats available that are hardly used at all, or are more specialized than AMA even. If anyone has a solution please advise. Thanks! Check out http://bibword.codeplex.com/wikipage?title=Styles . It contains a number of styles I developed for the Word bibliography tools. An attempt at AMA is among them. Yves -- BibWord :...

Repeating columns cuts off merged cells
Hi all, I hope you can help because I can't find a solution to this one. I've got a two page spreadsheet (side by side) and I'm trying to repeat columns A & B because their row headings on the left and should only appear when printed (split onto two pages) (as in print setup > columns to repeat at left > $A:$B), ...... but B57:I57 are merged and wrapped (another grief that merged wrapped cells don't auto height, but that's for another day) and so are B58:I58 and B59:I59 (footer things). ...... and when printed, B57:I57, etc. cuts off on the second page and...

Extra spaces in AutoSignature when replying to an HTML formatted e-mail
When inserting an AutoSignature in reply to an HTML e- mail, two extra spaces are inserted above the auto- signature. Is there something we can do to prevent this? I have looked at the formatting of the auto-signature and nothing is set to more than single spacing or paragraph before/after is set to 0". Thanks! ...