+ Reply to Thread
Results 1 to 61 of 61

VBA - Compare data of two sheets and highlight the matches

  1. #1
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    VBA - Compare data of two sheets and highlight the matches

    I researched a lot of forums but didn't get enough and exact answer for my problem.

    The thing is that I have two sheets both having a multiple rows and columns.
    In the midst of those columns, I need to compare two columns of sheet1 to two columns of sheet 2 and highlight if any of the data matches.
    Specification:
    Sheet 1: position of columns to be compared is fixed (Let's say at C and F).
    Sheet 2: position of columns to be compared is not fixed.
    Since sheet2 is always changing. (imported from other workbooks)
    Only the header of the columns to be compared is fixed.

    Below code doesn't have error BUT is not highlighting the matches.
    I don't know what's wrong with the code. BUT i didn't get any result.

    Please Login or Register  to view this content.

    Kindly help me solve this one.
    Thank you.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi there,

    The test:

    Please Login or Register  to view this content.
    will always return a value of False, because the cell interior colour was set to 16777215 just a few steps before.


    See if the attached workbook does what you need. It uses the following code (at first sight it appears more complicated, but it's better structured and lends itself to use with other worksheets, data columns etc.):

    Please Login or Register  to view this content.
    The highlighted values can be altered to suit your own requirements. Structuring the code in the above way means that if layout/highlighting requirements change, the associated code changes need to be made in one location only.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48
    Greg M,

    Thank you for your kindness. I will try your code.
    Last edited by Jpngineer; 07-22-2019 at 10:37 PM. Reason: quoted reply

  4. #4
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi,

    I tried your code.
    I am not so good at vba so I cant't understand it so well.

    Would you mind to see attached file.
    The problem is that it is not working when there are blank rows in between data.
    How would i fix it?

    Thanks
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    I will try again to study your code.
    What I needed is to highlight the matching data.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Set the conditional formatting
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi there,

    Good day.

    I tried using your code.
    But I cannot make it work.
    Would you mind to check?

    Thank you so much
    Attached Files Attached Files

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Working, but you have no matched data between each 2 columns...
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi jindon,

    I tried your code as well.
    I can't make it work as well.
    I paste it in the code window and use it as it is.

    Does it work with the sample sheet on your side?

    Thank you so much.
    Last edited by Jpngineer; 07-22-2019 at 10:36 PM. Reason: quoted reply

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

    Re: VBA - Compare data of two sheets and highlight the matches

    1) Please don't quote full post, forum doesn't like it.

    2) I think I misread your problem.

    Do you want to compare 図番 with DWG. and 簡略コード with SYM.?

  11. #11
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi jindon,

    This is the version without blank in between rows.
    There are matching values. But when i inserted blank rows
    above codes are not working.

    Quote Originally Posted by jindon View Post
    Working, but you have no matched data between each 2 columns...
    What do you think is the problem?

    Thank you so much.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi jindon,

    Sorry for the quoting.
    Yes I need to compare that.

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

    Re: VBA - Compare data of two sheets and highlight the matches

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

  14. #14
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi Jindon,

    Thank you for your help.
    It's now working.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    You are welcome.

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

  16. #16
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi Jindon,

    I have another problem inline with this code.
    I wanted to have a clear or reset function separately for Data-Master sheet and Data-Imported sheet.
    But my code for that is not working when partnered with your code.

    Thanks for your help.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    If you want to clear entire column add 2 lines in bold.
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi Jindon,

    I'm sorry for a wrong explanation.
    The sequence for what I am doing is this:
    1) Compare the highlight (SOLVED)
    2) Save as DATA - Imported sheet (OKAY)
    3) Reset the interior color of the DATA - Master sheet
    4) Import new file to compare

    I have a code for resetting of interior cells but it is not working when used with the previous code.
    Please Login or Register  to view this content.
    Thank you.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Not clear to me...

    Since the column(s) in 2nd sheet are not fixed, you want to reset all the columns?

  20. #20
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    I wanted to reset the interior color of the "Data - Master" sheet after I saved (to a new workbook) the "Data - Imported" sheet.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Do you mean remove conditional formatting when you save?

  22. #22
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Yes remove only the formatting of the "Data - Master" (Only the compared columns) because other columns have color formatting as well.
    Note: "Data - Imported" has been saved separately as new workbook without resetting the format.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Added one forms commadbutton (not Activex, better than ActiveX on the sheet) and macro assigned to it.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Jindon,

    Thank you so much.

    That works well.
    This is really SOLVED problem.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    No problem and thanks for the rep.

  26. #26
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hello everyone, Hello Jindon,

    I got another problem.
    After coloring the cell with yellow
    Can we automate the filter by color?

    Because when I manually filter by color, since there are blank rows, not only the yellow cells are shown.

    Thank you for catering all my vba-troubles.
    Last edited by Jpngineer; 07-23-2019 at 10:39 PM. Reason: wrong grammar

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

    Re: VBA - Compare data of two sheets and highlight the matches

    When you clear the cell color, do you want both sheet? or just "Data - Master"?

  28. #28
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi jindon,

    I want to filter the "Data-Imported" by color. (only the cell with highlighted yellow should be shown)

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

    Re: VBA - Compare data of two sheets and highlight the matches

    You said you want to remove conditional formatting when you save and currently removing only from Data - Master, so

    1) CommandButton1_Click
    Please Login or Register  to view this content.
    2) RemoveCF
    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    I wanted to filter the "Data - Imported" by color.
    I wanted ALL yellow cells only to be shown.

    However, its not working.

    Kindly see attached file.
    Thanks
    Attached Files Attached Files

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

    Re: VBA - Compare data of two sheets and highlight the matches

    See my previous post, you should be able to filter by color now.

    When the cells are colored via conditional formatting, filter can not detect the cell color, so no conditional formatting are used now.

  32. #32
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi jindon,

    There is no problem with the last time code.
    I just wanted to filter (sort) by color the "Data - Imported" (before saving to new workbook).

  33. #33
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Thank you.

    I will try.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Ahh, OK which column?

  35. #35
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    The columns with cells that have been highlighted with yellow at the Data - Imported.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Totally ignore my last codes and just add one line in bold.
    Please Login or Register  to view this content.

  37. #37
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Works well.

    Thank you so much

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Np...................

  39. #39
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi sir,

    I had an existing problem.
    c.AutoFilter 1, RGB(255, 255, 0), 8 '<--- this line only showed the filter button on 1column only
    Is it possible to show Autofilter button to both columns with colored cell?

    Thanks.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Try
    Please Login or Register  to view this content.

  41. #41
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    This works fine.
    But..kindly see attached file.
    I wanted to include in filtering all with yellow cells for both columns of datay even if only one column was colored yellow.
    Attached Files Attached Files

  42. #42
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi sir,
    Is the recent case not possible to be solved?
    Thanks

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Please Login or Register  to view this content.

  44. #44
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Thank you sir. It works well.

  45. #45
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches and copy remarks

    Hi Good excel experts,

    I know the thread is a bit long.
    But I got a new revision here.

    Background: I tried revising the syntax for an add up. But it appears to be difficult for a beginner like me.

    Here's the thing:
    After comparing and highlighting the cells, "remarks" column from datax should be copied to an empty cell in "datay".

    WOuld that be possible to achieve?

    If it's not too much, can you please explain shortly how the code works?

    Thank you so much.
    Wanna learn more here.
    Attached Files Attached Files

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Use VLookUp function, no other suggestion.

    If you want to understand the code, first, find out the lines that you understand and you don't.

  47. #47
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Thanks for the information.

  48. #48
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Please Login or Register  to view this content.
    Sir, I don't understand the meaning of the colored codes. especialy the meaning of this symbol (""""")
    If it's not too much can you explain that.

    Thank you so much.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    When creating a string, double quote used as a string within the string MUST be escaped by adding another double quote.

    "=and(" & xx & "<>"""",isnumber(match(" & xx & "," & y & ",0)))"

    Actually
    =and(" & xx & "<>"",isnumber(match(" & xx & "," & y & ",0))) in formula1 protocol in CF.

  50. #50
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Thanks for the informatin
    I have a code for vlookup here but i dont know how would it fit to what i need.
    I wanted to add it to the lookformatches() code
    Would you mind to take a look on it?

    Please Login or Register  to view this content.
    What do you think?

    Thanks in advance.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    When you ask this kind of question, you should state everything in your first post, because the columns are not fixed.
    Please Login or Register  to view this content.

  52. #52
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Sorry sir I didn't see that coming.
    Thanks for your patience and help.

  53. #53
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Sir Jindon,

    You're such an expert.
    It works perfectly.
    Thanks for taking some time doing it.

    心から感謝します。ありがとうございました。

  54. #54
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Hi Sir Jindon,

    I know this was already solved. But revisions here (Japan) is quite necessary.
    Anyway.. i am adding the
    Please Login or Register  to view this content.
    to rename the column header of datay where the Remarks are being copied.
    But instead it filled up the entire column with Remarks.
    Could you again share your expertise with me? Thank you

    Thanks for helping as always.

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

    Re: VBA - Compare data of two sheets and highlight the matches

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

  56. #56
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Ah It needs
    Please Login or Register  to view this content.
    Thank you so much.
    I learned a lot from you.

  57. #57
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    Good day sir,
    there is a problem with the latest code that I can't figure out.
    Maybe because of the UBound?
    When "datay" has been saved. The yellow cell color has been cleared out.

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Quote Originally Posted by Jpngineer View Post
    When "datay" has been saved. The yellow cell color has been cleared out.
    Impossible as long as you are talking about the same file.

  59. #59
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    I mean "datay" will be moved and saved as xlsx file.
    When moved and closed the cell color is still yellow..
    when opening datay (the moved and saved version).. the filter is as is but the cell color are cleared out.

    Or maybe there is something wrong with my excel?

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

    Re: VBA - Compare data of two sheets and highlight the matches

    Of course.

    The code is specially designed for your question, but you are asking one by one. I hate it.

    I will not do it anymore, sorry.

  61. #61
    Registered User
    Join Date
    07-22-2019
    Location
    Japan
    MS-Off Ver
    2016
    Posts
    48

    Re: VBA - Compare data of two sheets and highlight the matches

    That is the answer to your question. I am just explaining it.

    Thanks anyway..let's leave it there.

+ 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: 10
    Last Post: 03-10-2017, 12:08 PM
  2. [SOLVED] Vba to compare two sheets for matches and if found Move all data pertaining.
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-21-2016, 02:52 PM
  3. [SOLVED] Compare two Worksheets and Highlight Matches
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-20-2012, 12:20 PM
  4. Compare Cells from 2 WorkBooks and Highlight matches in current.
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-31-2011, 06:31 AM
  5. Compare two named ranges and highlight matches
    By jbmerrel in forum Excel General
    Replies: 1
    Last Post: 12-29-2011, 06:53 PM
  6. Compare two Sheets - Add New Data, Update Differences, Highlight Void Data
    By R_S_6 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-18-2010, 04:03 AM
  7. compare lists, highlight matches
    By trixxnixon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-05-2009, 11:30 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