+ Reply to Thread
Results 1 to 11 of 11

VBA To Compare and Highlight Cells that were missed out

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    29

    VBA To Compare and Highlight Cells that were missed out

    Hi,

    I have attached a Sample Workbook, wherein there are 2 Sheets.
    Sheet 1 is with the Original Data and Sheet 2 is the data that we have received, where a person has manually highlighted the differences.

    The problem is that few differences haven't been highlighted. They are missed out.
    We have around 20-25 Workbooks with 25K or 50K lines in each.

    Can someone help me out with a macro that can highlight the pending differences in other color without highlighting the changes that are already done??
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA To Compare and Highlight Cells that were missed out

    Can anyone help me out! please...

  3. #3
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA To Compare and Highlight Cells that were missed out

    I just wanna know if this is possible or not... can someone please reply??

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,133

    Re: VBA To Compare and Highlight Cells that were missed out

    It is fairly easy to highlight the cells that don't match, but to highlight the specific characters that don't match is actually quite a complicated job. Can you explain how this will be used? We might come up with an approach that is easier to do but still meets your business requirements.

  5. #5
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA To Compare and Highlight Cells that were missed out

    Hi,

    Thanks for replying!
    Actually we already have a big database of Addresses of our customers in our software.. and all these addresses were updated by each customer in a form that we had provided them.
    Few people were assigned to update these addresses in an Excel sheet and they had to update and manually highlight the changes which they did. This excel would then be directly uploaded in software.

    Now a lot of them have made mistakes in highlighting the changes.. they have made changes but have forgotten to highlight, and now we have to find the missed out highlights and update the excel... there are many sheets with over 50K or 80K lines.

    Our manager doesn't want us to replace the existing highlights. They want it to be as it is.

    For the next project I have already got a macro done that would highlight the differences.. (thanks to Excel forum!) so that no manual work is to be done.. People will simply update the address or any other data and the macro will do the job of highlighting the differences.... but... for the existing problem I really need a solution... please help!

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,133

    Re: VBA To Compare and Highlight Cells that were missed out

    I'm trying to understand why you need each individual character to be highlighted if you already the complete correct address information.

  7. #7
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA To Compare and Highlight Cells that were missed out

    Hi,

    Okay.. the purpose is our team wants to know how many changes we have found in the updated list...
    Each person who has updated the data, fills in their name at the end of the column with a remarks column that says what difference they have found in the address.. like Address changed (from one area to another) or Shifted to so and so... from one city to another or Mobile number changed / updated etc. etc... whatever changes they have found in that particular line.. they have to mention it in remarks column.

    In sample book I have given only 3 or 4 columns... but we have 31 columns...where address 1,2,3, county, state, mobile no., email ID etc... are given.
    Our team will analyze what amount of difference have we found in data... like how many mobile numbers were updated or changed? How many have shifted to another city? etc. etc... it's for their analysis purpose.

    And one more reason is that they want to know how many of them have wrongly entered the data.
    We have list of Workbooks with addresses from different cities. If in City A a person has updated saying Shifted to City D, then that entire row will be shifted in City D workbook.

    The reason for highlighting the differences is that they want to know the amount of changes that took place.... and whether they should continue doing this exercise in coming years too!

    Hope I have explained my situation well enough.

  8. #8
    Registered User
    Join Date
    12-08-2020
    Location
    unknown
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: VBA To Compare and Highlight Cells that were missed out

    before get into this issue, i think it is important to know, how to analysis the data

    if i could color different parts by unit of alphabets, how could the next analysis go on?

    it is manual way?

    or, why don't you check different parts by unit of cell?

    anyways, i think it need to be ai tech
    Last edited by chronjy; 12-23-2020 at 03:07 AM.

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,375

    Re: VBA To Compare and Highlight Cells that were missed out

    This will work for your current sample but slow for actual file of 50K rows +
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 12-23-2020 at 07:54 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  10. #10
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA To Compare and Highlight Cells that were missed out

    Amazing!!!
    This is what I wanted...!! It's perfect!
    Thanks a lot..... you have really saved my time!

    Excel Forum Rocks!

    Thank youuuuuuu!!!!

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,375

    Re: VBA To Compare and Highlight Cells that were missed out

    .........................
    Thanks.png

+ 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. Compile/Compare Repeating Lists and Mark Missed Items
    By singinwes in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-17-2020, 03:08 PM
  2. To compare texts in two cells and highlight the changes
    By nida18 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2015, 09:20 AM
  3. [SOLVED] Formula to highlight missed completion dates
    By AndyRic in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-11-2014, 12:28 PM
  4. Replies: 1
    Last Post: 12-13-2013, 10:28 PM
  5. [SOLVED] compare cells and highlight same
    By shart2k12 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-05-2013, 01:34 PM
  6. Compare 2 cells in a worksheet and highlight if different?
    By loulouk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-17-2012, 01:44 PM
  7. Compare Columns and Highlight Cells
    By Daker3 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-13-2012, 11:22 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