+ Reply to Thread
Results 1 to 10 of 10

search & highlight the case sensitive text in one column that match a list on other column

  1. #1
    Registered User
    Join Date
    05-29-2020
    Location
    Chisinau
    MS-Off Ver
    2019
    Posts
    37

    Cool search & highlight the case sensitive text in one column that match a list on other column

    Hi guys... Can I get a VBA code that searches and highlights the case sensitive text in one column that matches short list (50 words) of the correct written text found on other column?

    I have more than 4000 names of whatever in the column that need to search inside the correct written words. They are duplicates, most of them, but that is not the problem. They should have been written in a specific way (I have a list of them), but someone manually wrote them differently. The filter cannot make the distinction between "MIKE" and "Mike" or "miKe", however VBA is case sensitive, so must be a solution.

    Can be solved without the need to invent and upload a sample file? Must be easy to understand what I ask. Thank you!
    Last edited by Alexander2020; 09-05-2020 at 01:11 PM.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,849

    Re: search & highlight the case sensitive text in one column that match a list on other co

    Well, if you want vba code, you can record your own macro.
    Then all you need to do is edit macro so that you can use if for other searches
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    05-29-2020
    Location
    Chisinau
    MS-Off Ver
    2019
    Posts
    37

    Re: search & highlight the case sensitive text in one column that match a list on other co

    Quote Originally Posted by Keebellah View Post
    Well, if you want vba code, you can record your own macro.
    Then all you need to do is edit macro so that you can use if for other searches
    Record doing what? There is no way to filter case sensitive in Excel.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,849

    Re: search & highlight the case sensitive text in one column that match a list on other co

    Who's talking about Filter I'm taking about Search.
    If you start recording a macro and then press Ctrl + F ( the find dialog) you can specify case sensitive or not and where to search.
    After the search is completed you stop macro recorder and look at what is written in the vba code; it serves as a starting point.

  5. #5
    Registered User
    Join Date
    05-29-2020
    Location
    Chisinau
    MS-Off Ver
    2019
    Posts
    37

    Re: search & highlight the case sensitive text in one column that match a list on other co

    1) I do not need to search, but to have a selection of the results... this is what a filter can do, or a conditional formatting, not the "Find/Replace" dialog
    2) In the Find dialog you can specify only one word (I doesn't help I can use *). I need to have a match with a list of words
    3) Recording a macro with a search for one word is giving only the selected column I searched within...
    Please Login or Register  to view this content.
    How this is useful?

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,188

    Re: search & highlight the case sensitive text in one column that match a list on other co

    Quote Originally Posted by Alexander2020 View Post
    Can I get a VBA code that searches and highlights the case sensitive text in one column that matches short list (50 words) of the correct written text found on other column?
    They should have been written in a specific way (I have a list of them), but someone manually wrote them differently. The filter cannot make the distinction between "MIKE" and "Mike" or "miKe", however VBA is case sensitive, so must be a solution.
    It fully depends on how you write the code, it can be case insensitive.

  7. #7
    Registered User
    Join Date
    05-29-2020
    Location
    Chisinau
    MS-Off Ver
    2019
    Posts
    37

    Re: search & highlight the case sensitive text in one column that match a list on other co

    @Keebellah... I understood what you were suggesting... Not only FIND... but making REPLACE and select a format as coloring the results. And I have a working code. This is sample of it, for only 2 words "ALBA" and "ARAD". I get them highlighted in yellow, while "Alba", "AlBa", "alba" or "Arad", "ArAd", "arad" are not. So I can put filter on color later.

    Please Login or Register  to view this content.
    Now... my question is... I have 50 words in my list... I can repeat the task for each of them, but is there a way to write this code in a more compact way? To give the list of the words inside brackets or something and inside other brackets the replacement (same word), but will do the trick to color them?

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,849

    Re: search & highlight the case sensitive text in one column that match a list on other co

    I'll see what I can do later this evening

  9. #9
    Registered User
    Join Date
    05-29-2020
    Location
    Chisinau
    MS-Off Ver
    2019
    Posts
    37

    Re: search & highlight the case sensitive text in one column that match a list on other co

    I have found this code on the net:
    Please Login or Register  to view this content.
    And I have adapted to my situation (I should mention that I have already a filter on that sheet with a selection on the column J, so I had also to remove the filter in my code):

    Please Login or Register  to view this content.
    Of course I made it for 3 words and I can add now all my list inside the code.
    I welcome anyone that wants to improve it (should be... maybe specify the range instead the selection... I am a totally newbie in VBA, so I believe there is place for improvement).
    Anyway... it works already for me this code... so the problem is SOLVED. Thank you Keebellah for suggesting to do recording macro on find/replace... I used multiple times the tool to find my way.

    How can be modified the code to point at the list of the words located in Sheet2 instead of including all the searched words inside the VBA code? You know... when you have 50 words like me, the code doesn't look so pretty.

    I wait 24 hours and if nothing new I will mark the thread as "Solved".
    Last edited by Alexander2020; 09-07-2020 at 01:52 AM.

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,849

    Re: search & highlight the case sensitive text in one column that match a list on other co

    Hi Alexander, II did not add any explanation to the code but this works for anything.
    In The worksheet FINDTEXTS are two list (named ranges)
    Column A the text to search for
    Column B the replacement text
    The search text is NOT case sensitive but will search anything you place in column A and replace it with what is in column B
    Sheet1 has a button to press.

    Column J is the column you used too and in Column I is a copy of J so you can see the changes
    Attached Files Attached Files

+ 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. Case sensitive conditional formatting single column
    By MikeTi in forum Excel General
    Replies: 4
    Last Post: 05-11-2020, 01:49 PM
  2. [SOLVED] Search filter and highlight string - non case sensitive
    By Steve@Rugby in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-03-2017, 11:43 AM
  3. [SOLVED] Search Row by Column value and Highlight Text - For each Row
    By Discloner in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-08-2014, 01:52 PM
  4. Highlight duplicate rows based on column A but be case sensitive
    By theblade24 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-18-2013, 06:14 PM
  5. [SOLVED] Find word(s) within text string that matches a list of keywords - NOT case sensitive
    By gbm222 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2013, 02:15 AM
  6. [SOLVED] Macro for Case Sensitive Text Search
    By vcs1161 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-23-2013, 11:13 AM
  7. Replies: 2
    Last Post: 05-27-2012, 09:43 AM

Tags for this Thread

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