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

taraloca · Aug 16th 2022

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:

Code

Sub CompareCols()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet
    Set WS1 = ThisWorkbook.Sheets("FDG Accounts")
    Set WS2 = Workbooks("Client Bill Info.xlsx").Sheets("Detailed Bill Info")
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In WS2.Range("A2", WS2.Range("A" & Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next
    For Each Rng In WS1.Range("A2", WS1.Range("A" & Rows.Count).End(xlUp))
        If RngList.Exists(Rng.Value) Then
            WS1.Cells(Rng.Row, 8).Interior.ColorIndex = 6
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Display More

Code: NEW UPDATE WORKING CODE

I have figured out the correct code to highlight unique values from WB1 AccountID that are not in WB2 AccountID**********************

I had to make the values compared a string with the Cstr()

'Method to show what AccountID is in FDG Accounts but not Client Bill Info.
Sub CompareCols()
    'Disabling the screen updating.
    Application.ScreenUpdating = False
    
    'Declaring variables
    Dim Rng As Range, RngList As Object, WB1 As Worksheet, WB2 As Worksheet
    
    'Setting values to variables declared
    Set WB1 = ThisWorkbook.Sheets("FDG Accounts")
    Set WB2 = Workbooks("Client Bill Info.xlsm").Sheets("Detailed Bill Info")
    Set RngList = CreateObject("Scripting.Dictionary")
    
    'Loop to collect values that are in column A of this workbook
    'that are not in column A of WB2
    For Each Rng In WB2.Range("A2", WB2.Range("A" & Rows.Count).End(xlUp))
        If Not RngList.Exists(CStr(Rng.Value)) Then
            RngList.Add CStr(Rng.Value), Nothing
        End If
    Next
    'Iterate through results and highlight the cell with the unique value
    For Each Rng In WB1.Range("A2", WB1.Range("A" & Rows.Count).End(xlUp))
        If Not RngList.Exists(CStr(Rng.Value)) Then
            WB1.Cells(Rng.Row, 1).Interior.ColorIndex = 6
            tmpStr = Rng.Value
            'MsgBox (tmpStr)
        End If
    Next
    Sheets.Add.Name = "myNewSheet"
    
    Application.ScreenUpdating = True
End Sub

Display More

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

**KjBox** · Aug 16th 2022

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.

Code

Option Explicit

Sub HighlightDifferences()
    Dim x, y, z, i&, ii&, iii&, oDic As Object
    Dim wbk1 As Workbook, wbk2 As Workbook, ws As Worksheet
    
    Set wbk1 = ThisWorkbook
    Set wbk2 = Workbooks("Client Bill Info.xlsx")
    Set oDic = CreateObject("scripting.dictionary")
    x = wbk1.Sheets("FDG Accounts").Cells(1).CurrentRegion.Columns(1)
    y = wbk2.Sheets("Detailed Bill Info").Cells(1).CurrentRegion.Columns(1)
    ReDim z(1 To UBound(x, 1) + 1, 1 To 1): iii = 1
    
    wbk1.Sheets("FDG Accounts").Columns(1).Interior.Color = xlNone
    With oDic
        For i = 2 To UBound(y, 1)
            If Not .exists(CSng(y(i, 1))) Then .Add CSng(y(i, 1)), Nothing
        Next
    End With
    For i = 2 To UBound(x, 1)
        If IsError(Application.Match(x(i, 1), oDic.keys, 0)) Then
            wbk1.Sheets("FDG Accounts").Cells(i, 1).Interior.Color = vbYellow
            iii = iii + 1
            z(iii, 1) = x(i, 1)
        End If
    Next
    If z(2, 1) <> vbNullString Then
        z(1, 1) = "Account ID"
        Set ws = wbk1.Sheets.Add(, wbk1.Sheets(wbk1.Sheets.Count))
        With ws
            .Name = "Missing Acc. IDs"
            .[a1].Resize(UBound(z, 1)) = z
            .Columns(1).AutoFit
            .Activate
        End With
    Else
        MsgBox "No Missing Account IDs", , "Missing Account IDs"
    End If
    
End Sub

Display More

taraloca · Aug 17th 2022

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.

**KjBox** · Aug 17th 2022

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.

taraloca · Aug 17th 2022

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?

**KjBox** · Aug 18th 2022

Sorry I misunderstood what you meant.

Code

Option Explicit

Sub HighlightDifferences()
    Dim x, y, z, i&, ii&, iii&, oDic As Object
    Dim wbk1 As Workbook, wbk2 As Workbook, ws As Worksheet
    
    Const sWSName = "Missing Acc. IDs"  'CHANGE IF YOU WANT A DIFFERENT NAME FOR NEW SHEET
    
    Set wbk1 = ThisWorkbook
    Set wbk2 = Workbooks("Client Bill Info.xlsx")
    Set oDic = CreateObject("scripting.dictionary")
    x = wbk1.Sheets("FDG Accounts").Cells(1).CurrentRegion.Columns(1)
    y = wbk2.Sheets("Detailed Bill Info").Cells(1).CurrentRegion.Columns(1)
    ReDim z(1 To UBound(x, 1) + 1, 1 To 1): iii = 1
    
    wbk1.Sheets("FDG Accounts").Columns(1).Interior.Color = xlNone
    With oDic
        For i = 2 To UBound(y, 1)
            If Not .exists(y(i, 1)) Then .Add y(i, 1), Nothing
        Next
    End With
    For i = 2 To UBound(x, 1)
        If IsError(Application.Match(CStr(x(i, 1)), oDic.keys, 0)) Then
            wbk1.Sheets("FDG Accounts").Cells(i, 1).Interior.Color = vbYellow
            iii = iii + 1
            z(iii, 1) = x(i, 1)
        End If
    Next
    If WorksheetExists(sWSName, wbk1) = True Then
        wbk1.Sheets(sWSName).Columns(1).ClearContents
    End If
    If z(2, 1) <> vbNullString Then
        z(1, 1) = "Account ID"
        If WorksheetExists(sWSName, wbk1) = False Then
            Set ws = wbk1.Sheets.Add(, wbk1.Sheets(wbk1.Sheets.Count))
            ws.Name = sWSName
        Else
            Set ws = wbk1.Sheets(sWSName)
        End If
        With ws
            .[a1].Resize(UBound(z, 1)) = z
            .Columns(1).AutoFit
            .Activate
        End With
    Else
        MsgBox "No Missing Account IDs", , "Missing Account IDs"
    End If
    
End Sub

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

Display More

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.

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

Participate now!

Share

Similar Threads

Looping trough all workbooks in a folder an their worksheet. Action is to paste special, so that there is no formula

excel vba Select Case loop with "and" possible?

Copy last 20 visible cells in auto filtered range

Tags

Users Viewing This Thread