+ Reply to Thread
Results 1 to 9 of 9

List of keywords to search from another sheet cell contains many other letters

  1. #1
    Registered User
    Join Date
    04-27-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    19

    List of keywords to search from another sheet cell contains many other letters

    Hi - I am looking to create a macro that would highlight the entire row of data if the cell contains the word.
    Ideally, looking to work with 2 sheets, one sheet ("Sheet1") contains the keywords and the other sheet ("Data") has the data.

    Sheet with data ("Data") may have many other words in the cell with the key word. Example in Sheet1=Keyword= "John Doe" and in the Data sheet ("Data") cell A6 contains "John Doe is a great man", A8 contains a man who is great is John Doe, or great is John Doe a man. the key word is absolute but want to find all instances by highlighting the row.

    How do I modify the below

    I have the following so far...

    Public Sub HighlightListedValues()
    Dim strConcatList As String
    Dim cell As range

    'Creates a string concatenating your list of strings, separated by |s
    'e.g. "item1|item2|item3|item4|"
    For Each cell In Sheets("Sheet1").range("A1:A8")
    strConcatList = strConcatList & cell.Value & "|"
    Next cell

    'For each used cell in Column A of sheet1, check whether the value in that cell
    'is contained within the concatenated string
    For Each cell In Intersect(Sheets("Data").range("A:E"), Sheets("Data").UsedRange)
    If InStr(strConcatList, cell.Value) > 0 Then 'InStr returns 0 if the string isn't found
    cell.EntireRow.Interior.Color = RGB(255, 255, 0) 'Highlights the row in red if value found
    End If
    Next cell
    End Sub

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: List of keywords to search from another sheet cell contains many other letters

    Submit an example workbook, with data, which shows what you want to accomplish.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,512

    Re: List of keywords to search from another sheet cell contains many other letters

    As stnkynts said, an example with before and after makes life so much easier.

    Run the code from the sheet with the keywords (Sheet1)
    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: List of keywords to search from another sheet cell contains many other letters

    FWIW:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-27-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    19

    Re: List of keywords to search from another sheet cell contains many other letters

    I have attached the a sample, if you run the macro it highlights the keyword in "Sheet1" in the "Data" tab. I am trying to make the macro capture all instances of the keyword when in a sentence and not just in an individual cell..As there are instances when the key word will be in a sentence in a cell. Example Row 5 should be highlighted because the keyword Mark is in the sentence "John Mark is Love"

    Thank you again ever so much for all the great leverage and help - definitely giving attributes to this site in my dissertation.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,569

    Re: List of keywords to search from another sheet cell contains many other letters

    Try
    Please Login or Register  to view this content.
    Last edited by jindon; 02-18-2015 at 10:26 PM. Reason: Fixed loop counter

  7. #7
    Registered User
    Join Date
    04-27-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    19

    Re: List of keywords to search from another sheet cell contains many other letters

    Thank you Jindon - Absolutely gorgeous! it works just fine.

    Kindest regards,

    Ian

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,569

    Re: List of keywords to search from another sheet cell contains many other letters

    You are welcome and thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  9. #9
    Registered User
    Join Date
    04-27-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    19

    Re: List of keywords to search from another sheet cell contains many other letters

    Task done

+ 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. How do I search a cell for keywords and add text
    By sealsseals06 in forum Excel General
    Replies: 7
    Last Post: 03-12-2014, 06:46 PM
  2. [SOLVED] Search for keywords and copy rows containing keywords to new sheet
    By lenorsk in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-15-2013, 06:54 AM
  3. [SOLVED] How to search across 2 columns for keywords from a list and copy each row to a new sheet?
    By keithtran12 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 04-01-2013, 05:16 AM
  4. Column keyword search using list of keywords
    By tshrader in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2011, 03:46 PM
  5. Macro to Search and Copy Multiple Keywords into Another Sheet
    By Bangarang in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-05-2010, 01:57 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