+ Reply to Thread
Results 1 to 3 of 3

Conditional format a list if some of its text is contained in another list

  1. #1
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office 365
    Posts
    259

    Conditional format a list if some of its text is contained in another list

    So what I'm looking to do here is compare two lists of items. One is a big list that has many different items and another list of what we will call key items. Some of the items in the first list might be different versions (ex. apple 1, apple 2, etc or orange I, orange V, orange XIV)

    The big list starts in A2 and goes to A50. The smaller list of key items is in C2:C15.

    What I am looking to do is have any particular item in the C2:C15 list light up a color if some variation of its name appears in the List contained in column A.

    So for example, column A might list the following:
    Apple 1
    Apple 2
    Apple 3
    Apple 4
    Orange I
    Orange II
    Orange III
    Orange IV

    In C2:C15, they wouldn't have these variations, it would just be a generic Apple or Orange with no numbers or numerals attached.

    So in this case, I would want those two items in column C to light up because at least some variation of them appeared in column A.

    Is there a way to do this?

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,706

    Re: Conditional format a list if some of its text is contained in another list

    yes, with C2 the active cell, you can apply a conditional format formula/rule to C2:C15 along the lines of:

    =($C2<>"")*COUNT(SEARCH(" "&$C2&" "," "&$A$2:$A$50&" "))
    and set format accordingly

  3. #3
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office 365
    Posts
    259

    Re: Conditional format a list if some of its text is contained in another list

    Thank you. This will do just fine!

+ 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. Replies: 2
    Last Post: 03-01-2019, 02:00 PM
  2. Conditional Format to Add List of Text Based Upon Cell Contents
    By mkedyr1969 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-09-2015, 11:58 AM
  3. [SOLVED] Looking for a formula that indicates text not contained in a list.
    By Travisty in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-20-2015, 09:58 AM
  4. Replies: 4
    Last Post: 07-28-2015, 01:54 PM
  5. Using VBA to perform wildcard filter for text strings contained in a list
    By DaveBre in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-01-2014, 10:49 PM
  6. Sum if a number is contained in a list
    By pauldaddyadams in forum Excel General
    Replies: 8
    Last Post: 12-03-2013, 02:30 PM
  7. Conditional Format ROW based on text in drop-down list
    By Kwillz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-19-2013, 12:08 AM

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