+ Reply to Thread
Results 1 to 11 of 11

Conditional formatting based on multiple text criteria

  1. #1
    Registered User
    Join Date
    03-01-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel Mac 2011
    Posts
    8

    Conditional formatting based on multiple text criteria

    I'm trying to set up conditional formatting in a spreadsheet where there are 3 possible correct letters that can be entered into a range of cells and if one of these letters is not entered then I would like the cell to be highlighted.

    To be more precise, it's an accommodation spreadsheet and in each cell if someone needs a room for that particular night (each night is represented by a separate column) then a letter is used to indicate what type of room they require i.e. "k" is a king room, "s" is a Suite and "t" is a twin. I don't want to force people to have to select from a list which would be the obvious solution to this as it is simply too cumbersome for the amount of data entry we do.

    However i want to ensure that if someone accidentally enters anything other than t,k or s then the cell is highlighted. We work in large spreadsheets covering months of dates so to manually have to find any errors is hugely time consuming.

    I've tried setting up OR formulas and NOT formulas but can't seem to find a solution.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Conditional formatting based on multiple text criteria

    Hi,

    Is this what you're after? Try typing anywhere inside the bordered cells...

    I hope this helps

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,011

    Re: Conditional formatting based on multiple text criteria

    Hi and welcome to the forum

    Another option would be to prevent the errors in teh 1st place. You could do this with Data Validation, usaing Custom, and entering this formula...
    =AND(B2="K",B2="S",B2="T")

    Using that, the cell will ONLY allow the letters you specified
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Conditional formatting based on multiple text criteria

    @FDibbins: great idea, I didn't think of that

  5. #5
    Registered User
    Join Date
    03-01-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel Mac 2011
    Posts
    8

    Re: Conditional formatting based on multiple text criteria

    Hi there

    Yes, that's exactly what I'm looking for but for some reason I can't make it work in my spreadsheet for some reason.

    I've copied the formula over into the conditional formula building tool but it's not working correctly. Are you able to please explain the exact steps maybe?

    I'm attaching my spreadsheet for ease of reference.

    Many thanks

    Tanya
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,011

    Re: Conditional formatting based on multiple text criteria

    Where exactly do you want this to be?

  7. #7
    Registered User
    Join Date
    03-01-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel Mac 2011
    Posts
    8

    Re: Conditional formatting based on multiple text criteria

    I'm obviously missing something very basic but am not able to make either solution work and it's probably to do with my lack of understanding of the main cell ref being B2 maybe?

    @ajryan88 when I copy your formula into the conditional formatting builder tool it works in certain cells but not in others and I can't pinpoint why.

    @fdbibbins your solution seems to be exactly what i'm looking for but when I enter it via validation it will either continue to let me enter whatever I like into the cells or won't allow any valid entry at all including the T, S and K

    Any suggestions on where I'm going wrong?

    All help much appreciated.

  8. #8
    Registered User
    Join Date
    03-01-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel Mac 2011
    Posts
    8

    Re: Conditional formatting based on multiple text criteria

    The first cell range I'm trying to make this work for is S8:CJ27

    Thanks

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,011

    Re: Conditional formatting based on multiple text criteria

    Hilite that entitre range
    select Data Validation from the Data Tools menu
    under "allow", select Custom
    in "formu;a, enter =OR(S8="K",S8="S",S8="T")

    My apologies for suggesting =AND(B2="K",B2="S",B2="T")

  10. #10
    Registered User
    Join Date
    03-01-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel Mac 2011
    Posts
    8

    Re: Conditional formatting based on multiple text criteria

    That's great :-)

    Thanks so much - I really appreciate your help.

    Kind regards

    Tanya

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,011

    Re: Conditional formatting based on multiple text criteria

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Use Conditional Formatting based on multiple criteria
    By jdmet135 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-25-2012, 09:11 AM
  2. Replies: 4
    Last Post: 01-20-2011, 01:31 PM
  3. Conditional formatting based on multiple criteria (needs to be in VBA)
    By Fidd$ in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 10-12-2009, 09:11 AM
  4. Conditional formatting based on multiple criteria
    By mainemojo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2009, 09:33 PM
  5. Conditional Formatting based on multiple criteria
    By twlove@ontuet.com in forum Excel General
    Replies: 2
    Last Post: 08-03-2006, 04:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1