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:
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
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
Now asking how I get a msgBox to display the values and/or put values on a new sheet