+ Reply to Thread
Results 1 to 6 of 6

Compare two columns. Highlight the item(s) that have more than one different match

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

    Question Compare two columns. Highlight the item(s) that have more than one different match

    Please help with a VBA code.
    I want to compare two columns with many many items.
    I need to highlight the items from column A that have more than one DIFFERENT match in the column B.

    In my attached EXAMPLE file:
    Apples are always red, oranges are orange and mango are yellow. Such cases would not trigger "the alarm".
    Same case with the strawberries. Since it is only one match in the other column, would assume it is correct (all strawberries are red).
    In case of the bananas (or the kiwi), there are more than one (color) match for them in the other column. These are the cases that require my attention and need to highlight them.

    I don't know if my problem can be solved easier with some rule in conditional formatting or Excel functions and not a VBA code. Thank you in advance for any help!
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,440

    Re: Compare two columns. Highlight the item(s) that have more than one different match

    Can't do the VBA at the moment, but a formula solution is:

    Cell C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied down.

    My VBA solution would simply be to drop the formula into C2:Cx and convert to values.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


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

    Re: Compare two columns. Highlight the item(s) that have more than one different match

    The solution is working perfectly! For me is good enough! Thank you so much!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,440

    Re: Compare two columns. Highlight the item(s) that have more than one different match

    You're welcome. Thanks for the rep.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,440

    Re: Compare two columns. Highlight the item(s) that have more than one different match

    Just thought, for Conditional Formatting, you could use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,440

    Re: Compare two columns. Highlight the item(s) that have more than one different match

    This is an updated sample workbook.
    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. Compare two columns and highlight cells with same values (Considering one-to-one match)
    By jahanzaib447 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-03-2019, 11:11 PM
  2. Replies: 6
    Last Post: 11-02-2018, 06:56 AM
  3. Replies: 2
    Last Post: 11-27-2016, 05:56 PM
  4. Compare duplicate values in two columns and highlight if match
    By JEKF in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-18-2015, 12:32 PM
  5. compare columns and highlight
    By Goodstart14 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-05-2015, 12:45 PM
  6. Highlight a group of row if it has 4 same consecutive item in its columns
    By exlgh91 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2013, 03:48 PM
  7. [SOLVED] compare values in two cells, highlight when there is no match.
    By frhling in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-03-2013, 09:29 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