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?
Posts by taraloca
-
-
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.
-
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
Display MoreSub 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 SubCode: NEW UPDATE WORKING CODE
Display MoreI 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 SubNow asking how I get a msgBox to display the values and/or put values on a new sheet