How to Compare 2 Columns in 2 Separate Workbooks and Highlight Columns in WB1 that are Not in WB2

  • Hello,

    I am trying to highlight all the cells values that are in workbook 1(FDG Accounts), column a, that are not in workbook 2(Client Bill Info), column A. I am attaching the 2 workbooks to this thread, as well as showing the code I have written. I get no error, but I also get no highlights and I purposely made the last cell in column A different but it is not highlighted. If I can get this to work, I then want to make a new sheet in FDG Accounts to display the names of the accounts that are in it and not in Client Bill Info.


    Here is my code:


    Now asking how I get a msgBox to display the values and/or put values on a new sheet

  • The reason your code was not working is because the Account IDs in the FDG Accounts were numerical and in Client Bill Info they were preceded by 1 or 2 zeros and formatted as text.


    Try this, I have included code which will also add the required additional sheet in FDG Accounts.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited once, last by KjBox ().

  • KjBox I tried to format the Client Bill Info as a number without decimals and nothing happens when I select the column, right click, choose Format Cells, and then Number format. Can you tell me why it is not taking the formatting I am choosing?


    I see in the script you wrote you are using CSgn which will format. I also noticed that this script is not working, for example, 181910000 is in FDG and not in Client Bill Info. but was not included on the list nor highlighted. I need exact matches. Some of them appear to contain a match like Client Bill Info has 8181910000 and this contains the 181910000 but is not an exact match. The leading zeros are intentional and not to be ignored either. 181910000 is in FDG and 0181910000 is in Client Bill Info, they are different.


    Back to the drawing board for me. Any help is appreciated.

  • Quote

    The leading zeros are intentional and not to be ignored either.

    In that case an Account ID in FDG Accounts is never going to be matched in Client Bill Info.


    You need to have Account ID formatted the same in both sheets.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • This is not true. I merely needed to use Cstr() with the value as I documented an update to my initial questions with the working code. I still do not think it is grabbing unique values though, that is values that are in WB1 that are NOT in WB2. Any ideas?

  • Sorry I misunderstood what you meant.


    I would not recommend using a message box to display the missing Acc. IDs, your sample file has 43 Account IDs in FDG Accounts that do not appear in Client Bill Info, that would be too long a list for a message box.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited 3 times, last by KjBox ().

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!