We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Excel conditional formatting

Options
I’m trying to highlight cells in a column which contain one of a series of words I.e. turn the cells red if they contain any of the words eggs, tomatoes or bread or highlight blue for cheese, milk or yogurt. Is there a way to get excel to do it for me or do I have to do conditional formatting on each of the words individually.

Hope this makes sense.

Comments

  • DoaM
    DoaM Posts: 11,863 Forumite
    10,000 Posts Fifth Anniversary Name Dropper Photogenic
    You might be able to do it by creating Ranges (lists of words which you associate with a Range name) and then use the Ranges as part of the Conditional Formatting rules.

    https://www.extendoffice.com/documents/excel/3793-excel-conditional-formatting-search-for-multiple-words.html
  • Heedtheadvice
    Heedtheadvice Posts: 2,767 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    edited 8 December 2018 at 4:12PM
    Not sure if you still require an answer as you did not respond to Doam yet, Kido!!


    Just in case here is a simple solution for when the text to search through is non too big. If it is big than this method becomes a bit difficult to manage and a list or lists in a range of cells should be preferred.


    1. Type in the words to be matched such as "eggs, tomatoes or bread or" into a cell and name it "First_list". Type the second text ("highlight blue for cheese, milk or yogurt") in the next cell down and name it Second_List"


    Add more cells with searchable text as desired such as "cream, water champagne pepper" and name them accordingly. It might be useful to have meaningful names such as "Dairy", "Gluten_free" etc. instead of" First_List" etc.


    2. Select first cell to have it's contents searched for (e.g. "eggs") and select conditional formatting for the cell, new rule, use formulas..., and add in the following formula
    =IFERROR(SEARCH(A2,First_list),0)>0
    
    assuming a2 is that cell. After setting format you desire such as red fill then OK i,e select red matches: finds matches for text of A2 in cell named First_List

    Instead of typing the name of the named cell ("First list" etc.), highlight the bit of the function (above) for the cell to be searched (First_list in the above case) and just press F5 function key to get a menu of all named cells to select the correct one and avoid typos etc.


    3 on same cell conditional formatting, manage add a "New rule" and formula
    =IFERROR(SEARCH(A2, Second_list),0)>0
    
    and format such as blue fill and OK again. i.e. select blue matches. Ditto blue if in Second_list.


    4 Add more conditions as per step 3, if required


    Some points:
    SEARCH does the location finding and returns the position or an error code if not found. The IFERROR function converts that error code to zero and so the greater than operator tests for location found or not.


    SEARCH is not case sensitive but if that case is important use FIND instead.


    Using the cell reference means that it does not need to be the 'actual cell that contains the text to search for' that is formatted e.g. you could format cell B2 with the identical function.
    Use format painter to copy format to all other cells you need to test to produce a format but be aware that the formula as presented does not use absolute addressing so you might need to include some $ signs if not copying just vertically or horizontally.
  • esuhl
    esuhl Posts: 9,409 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    Kido wrote: »
    I’m trying to highlight cells in a column which contain one of a series of words I.e. turn the cells red if they contain any of the words eggs, tomatoes or bread or highlight blue for cheese, milk or yogurt. Is there a way to get excel to do it for me or do I have to do conditional formatting on each of the words individually.

    Hope this makes sense.

    If you have the list in column A, create a new empty column B.

    At the top of column B (cell B1), type:
    =IF(OR(A$1="eggs",A$1="tomatoes",A$1="bread"),"R",IF(OR(A$1="cheese",A$1="milk",A$1="yogurt"),"B"))
    
    Then apply conditional formatting on column A. If the adjacent cell in column B is "R", then make column A red, if it's "B" then make it blue. You can hide column B so that it not visible.
  • As can be seen with Excel many ways to skin a cat!


    You can always use a list and the Match function or...or......
    Really depends on need and preference.


    Esul, can you tell me how you make the code box in a post as that is a great way to present it?
  • esuhl
    esuhl Posts: 9,409 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    Esul, can you tell me how you make the code box in a post as that is a great way to present it?

    Sure -- you just use CODE tags. They're designed for entering computer code, so won't automatically insert spaces, convert characters to smilies, etc.


    Like this (just remove the spaces either side of the word CODE):


    [ CODE ]Code goes here[ /CODE ]
  • Heedtheadvice
    Heedtheadvice Posts: 2,767 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    edited 8 December 2018 at 4:12PM
    Thanks they really aid clarity.
    Added in my post above.
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.9K Banking & Borrowing
  • 253.1K Reduce Debt & Boost Income
  • 453.5K Spending & Discounts
  • 243.9K Work, Benefits & Business
  • 598.7K Mortgages, Homes & Bills
  • 176.9K Life & Family
  • 257.1K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.