+ Reply to Thread
Results 1 to 18 of 18

Highlight Cell for Partial Match Across Multiple Columns

  1. #1
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Highlight Cell for Partial Match Across Multiple Columns

    Hello! I'm hoping someone could help me.

    In the attached sample spreadsheet, I need vba to highlight any cell that has a partial match of the P# text to any other cell across three columns. The data may or may not be in the same row or the same column but it will always start with a capital 'P' with a four digit number.

    For example:
    P8448 appears in A2 and A4 and C2 so I want all three cells highlighted
    P8621 appears in A3 and B2 so I want both cells highlighted

    Thank you!

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight Cell for Partial Match Across Multiple Columns

    One way.
    Tried to separate the color, but some are overlapping, so doesn't make sense.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    Thanks...this is a great start! The one color is fine. You gave me exactly what I asked for but as I use it, I see I need to tweak my request.

    1-I only had one worksheet in my sample workbook but my production workbook actually has several worksheets. I would like for it to do this process in only one of my worksheets in my workbook.
    2-My sample uses columns A-C but my production workbook needs to compare columns O, P, and T.
    3-I have put your vba as a module and then called it to run when open the workbook, but I actually need it to run when I open the workbook plus as soon as I leave any one of the cells in columns O, P, or T. Basically, I would like for it to highlight as soon as I enter or tab from one of those cells (that may or may not have a duplication P#) and also un-highlight if I go to one of the highlighted cells, change the P#, then enter or tab off that cell.

    Are you able to update the vba for these adjustments?

    Thank you!

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight Cell for Partial Match Across Multiple Columns

    1) To ThisWorkbook code module
    Change sheet1 to actual worksheet code name (not the worksheet name that appears on sheet tab)
    Please Login or Register  to view this content.
    2) Sheet1 code module
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    It works except I need it to use columns O, P, and U (instead of T). I thought I had the columns updated correcting in the second code you provided but it didn't work. I've attached version two of the worksheet to help fix it. Thank you!
    Attached Files Attached Files

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

    Re: Highlight Cell for Partial Match Across Multiple Columns

    Col.U or Col.V?

  7. #7
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    Uggh...sorry, column V.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight Cell for Partial Match Across Multiple Columns

    Try change to
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    This works! One last change--could you update it to start with row two instead of row one? Thank you.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight Cell for Partial Match Across Multiple Columns

    Insert one line bold
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    This is beautiful....thank you! One last change, I PROMISE. I just realized I may need it to look for P with five digits instead of P with four digits because I will soon be reaching P10000 and so on after P9999. Is the vba looking for the number of digits? If so, could you let me know how to adjust it to accommodate P with four OR five digits?

    Thank you so much!

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight Cell for Partial Match Across Multiple Columns

    Doesn't matter.

    It searches "P" with number(s).
    If you want to restrict only 4 or more digits, change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    This is outstanding!!! Thank you!!!

  14. #14
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    Clarification:
    I came across a problem. When I filter for the red cells, then un-filter, it removes the highlighted colors in the heading (row one) which is why I didn't want it searching and highlighting row one for the "P" + numbers. Do you know how I can fix this?

    Thank you.
    Last edited by KolKon; 03-11-2021 at 10:50 AM.

  15. #15
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    After further testing (regardless of filtering like previously mentioned), the entire header row (row 1) in columns O thru V resets to no color when any cell in those columns changes to red. Is there a way to not reset the color in the header row 1?

    Thank you.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight Cell for Partial Match Across Multiple Columns

    Can you change 2 lines
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    This is perfect!!!! Thank you for your help!!!

  18. #18
    Registered User
    Join Date
    10-17-2019
    Location
    Kentucky
    MS-Off Ver
    2019 Excel
    Posts
    33

    Re: Highlight Cell for Partial Match Across Multiple Columns

    I need to update this request. I thought I could figure out how to change the vba but apparently not.

    I've attached an updated workbook. Please let me know how to update the vba to look in more columns for duplicated P#s (columns P, Q, W, X, and AD). Each year, I'll add another set of columns which will mean two new columns to look for duplicates.

    Thank you.
    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. [SOLVED] Find partial match between two columns and highlight cells that match
    By TomToms in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-18-2019, 01:07 PM
  2. Replies: 1
    Last Post: 06-22-2019, 02:09 PM
  3. Compare Date both Full match/ partial match between two columns
    By cyboincomp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-21-2018, 03:02 AM
  4. [SOLVED] Lookup partial values and if match return partial value from another cell
    By Renejorgensen in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-01-2017, 07:53 AM
  5. Highlight Partial Match using function
    By sivagopi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-15-2015, 05:21 AM
  6. Replies: 20
    Last Post: 01-02-2011, 10:00 PM
  7. Replies: 2
    Last Post: 10-30-2007, 12:12 PM

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