Using Logical OR on text in Conditional Formatting

I have a column with various text entries.  I have created a conditional formatting rule for each type of entry but the formatting can be grouped together, so I would like a Conditional Formatting rule that says:

If string contains "foo" or "bar" then colour red
If string does not contain "yibble" or "wibble" then make bold
etc.

Any idea how I can do this rather thasn having to manage over 30 rules with each one looking for a single text entry type.

Thanks

Darius
0
Darius
2/15/2010 11:55:11 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
1482 Views

Similar Articles

[PageSpeed] 37

Try a formula of

=OR(A2="foo",A2="bar")

=AND(A2<>"yibble",A2<>"wibble")

HTH

Bob

"Darius Poli" <user@msgroups.net/> wrote in message 
news:eHeQ7WjrKHA.4828@TK2MSFTNGP05.phx.gbl...
>I have a column with various text entries.  I have created a conditional 
>formatting rule for each type of entry but the formatting can be grouped 
>together, so I would like a Conditional Formatting rule that says:
>
> If string contains "foo" or "bar" then colour red
> If string does not contain "yibble" or "wibble" then make bold
> etc.
>
> Any idea how I can do this rather thasn having to manage over 30 rules 
> with each one looking for a single text entry type.
>
> Thanks
>
> Darius
>
> ---
> frmsrcurl: http://msgroups.net/microsoft.public.excel.worksheet.functions 


0
Bob
2/15/2010 12:38:28 PM
Hi,

CF rule for is there

=OR(ISNUMBER(SEARCH("Foo",A1)),ISNUMBER(SEARCH("Baa",A1)))

CF rule for both not there
=AND(NOT(ISNUMBER(SEARCH("Foo",A1))),NOT(ISNUMBER(SEARCH("Baa",A1))))

CF rule for only 1 there

=OR(NOT(ISNUMBER(SEARCH("Foo",A1))),NOT(ISNUMBER(SEARCH("Baa",A1))))

-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Darius Poli" wrote:

> I have a column with various text entries.  I have created a conditional formatting rule for each type of entry but the formatting can be grouped together, so I would like a Conditional Formatting rule that says:
> 
> If string contains "foo" or "bar" then colour red
> If string does not contain "yibble" or "wibble" then make bold
> etc.
> 
> Any idea how I can do this rather thasn having to manage over 30 rules with each one looking for a single text entry type.
> 
> Thanks
> 
> Darius
> 
> ---
> frmsrcurl: http://msgroups.net/microsoft.public.excel.worksheet.functions
> .
> 
0
Utf
2/15/2010 12:50:01 PM
Thanks for the useful hints but I need a little more help I'm afraid.

Column A contains a list of text items and I want to colour them based on the entry, for each entry, so I cannot use references to individual cells.

I tried setting the CF as =OR($A$1:$A$nn="text",...) but that will format things based on the data in the entire column.

What I want to say is, for any cell, if that cell has text "xxx", format one way, if it has text "yyy", format a different way.  For a large range of cells with changing values.

Thanks

Darius 
0
Darius
2/15/2010 3:16:07 PM
I think the formula that I gave you will work if you are looking at full 
cell content, or Mike's formula if partial cell content. Just select all the 
cells at the start.

HTH

Bob

"Darius Poli" <user@msgroups.net/> wrote in message 
news:eNnUNHlrKHA.5036@TK2MSFTNGP02.phx.gbl...
> Thanks for the useful hints but I need a little more help I'm afraid.
>
> Column A contains a list of text items and I want to colour them based on 
> the entry, for each entry, so I cannot use references to individual cells.
>
> I tried setting the CF as =OR($A$1:$A$nn="text",...) but that will format 
> things based on the data in the entire column.
>
> What I want to say is, for any cell, if that cell has text "xxx", format 
> one way, if it has text "yyy", format a different way.  For a large range 
> of cells with changing values.
>
> Thanks
>
> Darius
>
>
> ---
> frmsrcurl: 
> http://msgroups.net/microsoft.public.excel.worksheet.functions/Using-Logical-OR-on-text-in-Conditional-Formatting 


0
Bob
2/16/2010 12:58:43 PM
Reply:

Similar Artilces:

Windowsupdate using Intranet?
Is there a way to setup a local server to use as the wundowsupdate site for a large # of pc's, but that has a small pipe to the internet? Try downloading the Software update services onto one server, use that the install onto PC's etc, using Group Policies cheers >-----Original Message----- >Is there a way to setup a local server to use as the >wundowsupdate site for a large # of pc's, but that has a >small pipe to the internet? >. > ...

Excel Data Querry using criteria from Cell
Hello, I have created a data query in Excel using DATA - IMPORT EXTERNAL DATA - NEW DATABASE QUERY and then selecting Access and browsing to an query within an Access database. This query returns all data into excel, but I would like to limit the criteria based on what a user has typed into a cell in the Excel workbook. I thought this could be accomplished by clicking DATA - IMPORT EXTERNAL DATA then PARAMETERS but the PARAMETERS option is grayed out. I can select EDIT CURRENT QUERY, but this just brings me into the Microsoft query, where I don't see any options to query based on...

using button to clear contents
I have finally found out how to add a button but now I am haveing a problem on having the button clear contents in certain cells does anyone know how to do this thanks for your help I do not know any codes for this I'll bet you could learn very quickly by using the macro recorder while you select some cells and edit>clear>contents. Here is a sample for a button from the Control Toolbox Private Sub CommandButton1_Click() ActiveSheet.Range("certain cells").ClearContents End Sub "certain cells" would be your range of cells. Gord Dibben MS Exc...

Importing large tab-delimited text files?
I deal with very large data sets that are in tab-delimited .txt format. Many times, there are more than 256 columns of data. The Microsoft Knowledge Base had an article titled "Sample Macro to Import Data with More Than 256 Fields or Columns". This could do the trick for me if I could modify the macro to accomodate tab-delimited formats. Q1: Could anyone tell me how to modify the macro described above for tab-delimited .txt files? Q2: Does anyone know of a bulk file conversion utility that would change a tab-delimited text file to a CSV format? Thanks a bunch, Mark R-S Ma...

How do I print a text file ?
Hi, I am new to the group and wonder if any of you could offer any advice. I am trying to print a text file directly from my visual c++ application to the default printer without displaying the print dialog What is the best way of doing this ? If it is not possible to print directly from a text file, what commands to I use in between CDC::StartPage () To print the following Hello, my name is Joe Bloggs How are you I hope you can help with this problem etc..... CDC::EndPage () Thanks in advance Paul Paul, More info on printing than you can comfortably shake a stick at: http://www....

PROBLEM SIZING A TEXT BOX IN A FORM
Access 2007/vista I have a form in which I cannot adjust the text boxes INDIVIDUALLY. I click on a text box and it becomes surrounded by an orange border, indicating it is selected. But when I size it smaller or larger, all the other text boxes in the form move in tandem. I have not encountered this before and am baffled. Ideas? -- William ...

Using icon sets with relative references
I am using Excel 2007 in the Vista OS. I am trying to use conditional formatting with an icon set in a column but Excel will not allow relative references. Example: A1=10 min B1=20 max C1=30 oh D1=a formula { =IF(C1=0,B1,(B1-C1)) } The condtional formatting of D1 would be: if the value showing in D1 is >B1 the cell would show a red 'X', if the value showing in D1 is <A1 the cell would show a green 'check mark', and if the value showing in D1 is between the values in A1 and B1 it would show a yellow 'exclamation point'. I can get the D1 ...

Format problem #6
Somehow my formatting appears to be defaulting to time. Everytime I copy formulas or data from one cell to another the format changes to time. How do you stop this from happening? -- Gary If you land the cell pointer on a particular cell and cut-and-paste, then the format of the original cell will follow to the pasted cell. If you're encountering "phantom" formats then the cell may have been previously formatted. I tried to get a blank sprdsht to behave in the manner you describe, and couldn't. My error: this often happens to me when using cells formatted as dates or...

Using external email address for AD user
We have certain users that we would like to use an external email address for and give access to resources in the domain. The only solution I have found so far is to create an account they will use for access and then to add a contact so we can send emails and add as a member of DL's. Ideally, I would like to do this without adding 2 separate objects in AD. Is that possible? Hi there, Are you aware that you can create a user and mail-enable them (not mailbox-enable), establishing an external address on the account. It removes the need for both a contact and a user accoun...

HOW IS F3 USED WTH EXAMPLE
hi guys reply me soon 1) In A1 type a number such as 12% 2) With A1 still selected, in the Name box (box to left of Formula Bar) type the word taxrate and press Enter. You must press Enter for this to work! 3) Let's confirm that you have named the cell A1: click on cell D1, now click on cell A1. The name box should not display "taxrate". If not, repeat steps 2 4) In A2 type a number like 100 5) In B2 type =A2* and then tap the F3 key. A dialog box opens, select the entry "taxrate" and click the OK key. Another way is to double click the "taxrate" entry....

Predicting new Ys given new Xs using known relationship for X and
Hello. I know people have posted similar questions about using an existing relationship between X and Y to predict new Ys given new Xs but I think mine has a slightly different twist. I would appreciate any help that could be offered. I have 20 data points for both X and Y. Each X point represents the number of workers on a farm for a particular year over a 20-year period. Each Y point represents the amount of crop harvest each year over that same period. The data is from 1901 to 1920. When I plot the points and fit an exponential trend line I get a high R-square of .9 . I would li...

Can you change the way quick parts and/or auto text displays?
Is there a way to change the look of the drop down menu for quick parts and auto text so it does not take up so much space? Something like it looked in 2003? Assuming that you have discovered the building block organizer - see http://gregmaxey.mvps.org/Buiild_Employ_Custom_BB_Gallery.htm Like 2003 it ain't! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<&...

creating text box dynamically in ms access
can any body please tell me the code to create text boxes dynamically or else please give me the link abt that information Thanq See help on CreateControl In general, this is only useful if you are creating some kind of wizard form that creates whole forms for end users. You don't want to be creating text boxes on the fly for a normal form. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ravindar thati" <ravindarjobs@gmail.com> wrote in mes...

transfer contacts using email
Is it possible to transfer contacts from one machine to another using email? If, yes, what does the receiving person need to do? Many thanks, Frank FJB <FJB0623@aol.com> wrote: > Is it possible to transfer contacts from one machine to another using > email? If, yes, what does the receiving person need to do? Create a new PST (File>Net>Outlook Data File). Now, right-click Contacts and choose Copy. SPecify the new PST as the destination. When the copy completes, right-click the new PST and choose Close. Close Outlook. Rename the PST's file extension to something...

text conversion to number on select query
Hi, In a Select Query I'm joining 2 tables by Item ID (unique value, similar to Social Security Number) but 1 table created by IT has Item ID as a "number" value and the other table has it as a "text" value. How can I in a Select Query, create a formula that can either have the text as a number value and vice versa so i can link the 2 without getting "type mismatch in expression." I think I can use Cdbl Value or something like that in the formula but not sure. Thanks! "inspirz" wrote:subed going to jail > Hi, > ...

Using Dependents
In a particular instance when I am using the dependents search facility, a small symbol like a table or an Excel sheet appears, (the dependent arrow pointing to it). It's as if it is indicating that some other sheet has data dependednt on the cell. But I can't find any other sheet with dependent data. Double-click the arrow to the sheet symbol and Excel will display the dependent cells -- Kind regards, Niek Otten Microsoft MVP - Excel "Zembu" <Zembu@discussions.microsoft.com> wrote in message news:352631A7-0BF4-451D-8781-669C26270BF6@microsoft....

Functionality lost by using exchange
Our institution recently changed from provider based internet mail (pop3) to letting external mail arrive directly to our exchange 2003 server. The following problem shows up: in the old scenario, groups had 2 mailboxes: one for the group (G) and a private mailbox for the manager (M), each with their own e-mail address. I emulated this by creating a mbx-enabled user G, that is only used for the mailbox, not for login. I gave all group members including M access to that mailbox. But what happens? 1. If a group member sends a mail, the return address is the address of the sender; it should b...

Use query for Row Source?
I attempted to use a combo-box to look-up records on a form. The RowSource query that is generated by the wizard included 2 data fields. I wanted to show only a distinct list of the second, non-key data element (col1). When I changed the RowSource query to SELECT distinct Col1, the combo-box displays nothing. I then created a stored query with the SELECT distinct Col1. They query runs correctly, but when I put in in the RowSource, it displays nothing. Any clue what I could be doing wrong? On Fri, 26 Oct 2007 12:17:03 -0700, JHC wrote: > I attempted to use a combo-box to look-u...

Preventing auto-formatting when Replacing
Say I have a cell that contains LED2/14 The cell is formatted as Text. If I use Ctrl-H to replace LED with nothing, Excel insists on reformatting the remaining 2/14 as a date (i.e. 14-Feb). Is there any way to prevent this infuriating behavior? Thanks, Frank How about: Edit|Replace LED with ' (apostrophe) Frank Marousek wrote: > > Say I have a cell that contains > > LED2/14 > > The cell is formatted as Text. > > If I use Ctrl-H to replace LED with nothing, Excel insists on reformatting > the remaining 2/14 as a date (i.e. 14-Feb). Is there any way to...

Statement Format
Does anyone have or know of where to find a statement template that only has the transaction number and amout. Customer does not want all the item detail that has already been printed on the invoice. How can you modify the statement? Could not find anything for statements at Customersource. Thanks. -- SG Where do you get these reciept formats from? David "Rob" <roba@2020its..nospam..com> wrote in message news:eNa9RVWUGHA.4764@TK2MSFTNGP11.phx.gbl... > Try the attached file. > > You must use a NewsReader, such as OutlookExpress, in order to see the > at...

How to use Hotmail with Oulook?
I have managed to check my hotmail email with outlook. I was wondering if there was any way to make hotmail the default, so that I dont have to navigate to the hotmail folder? Can I use outlook's default inbox as the place where all my hotmail will go to? "J. Peterman" <justoNOSPAM316@hotmail.com> wrote in message news:bgvbp1$d3c$1@nnrp.waia.asn.au > I have managed to check my hotmail email with outlook. > I was wondering if there was any way to make hotmail the default, so > that I dont have to navigate to the hotmail folder? Can I use > outlook's defaul...

Can I use MS Groove with my Macbook?
I have students using Groove for their graduate team work and the Mac users are struggling to stay up in my classes. Any advide about the Mac interface with Groove 2007 issues? You will get better results by posting to the groove newsgroup directly On the web: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.groove -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. http://www.microsoft.com/protect "Grooving Professor" <Grooving Profe...

Properly Formated Spreadsheet
I have been given the task of training all our office worker how to properly format an Excel spreadsheet. Since I am a self taught Excel user I wasn't trained on the "proper" spreadsheet design. I am looking for a book or a good web site that will teach that. I am not even sure how a spreadsheet is properly designed. I was told it should list variables, assumptions and formulas. Any one ideas how do this? Thanks, Marty Marty Try Professional Excel Development http://www.amazon.com/exec/obidos/tg/detail/-/0321262506/qid=1119301729/sr=8-1/ref=pd_bbs_ur_1/104-7156060-962...

adding Text to the beginning of a control source
I want to say ="HELP"&Format([Session Start Date 2],"yyyy\:mm") & [ID] I want the word HELP then the start date 2 and finally the ID ie: HELP2010:12430 How do I put the text at the beginning? Where are you putting this? In the Control Source of control on a form/report? Then it should work as posted provided that [Session Start Date 2] and [ID] are in the object's record source. In a query? Then you just need a field alias and drop the = sign; MyHelpText: "HELP" & Format([Session Start Date 2],"yyyy\:mm") & [ID] ...

Formatting #22
Excel 2002 I have a sheet that contains numeric and date information. The date is being shown as the integer value ie 38384 instead of 01/02/2005 (this is shown in the formula bar). I can't change number formatting - increasing the number of decimal places doesn't change the value displayed (this is shown as a cell tip however). Any functions ie Autosum inset the function, not the result, for example if I insert autosum for 8 columns it displays '=SUM(I4:I11)' and not the result. I can change the 'text' formatting - bold,underline,italic. I can change the shading...