Posts by Moorthy24

    mycomputerguy-w , thanks for notifying, i will use </> to insert the column from next time. I understood that I must insert column I manually to update the word 'matching' using script. I have tried that, I scan see only the matching cells are updated in column 'H', but column 'I' not updated with word 'matching'. I'm attaching the copy of the file.


    @Mump yes, I'm looking for way to update column I with the word matching.


    Thanks.

    Files

    I have inset the code as suggested, below the line of Set RngList = CreateObject("Scripting.Dictionary"), it is not working. Here is the complete code, please correct if anything wrong.


    Sub CompareColsaddcol()

    Application.ScreenUpdating = False

    Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet, x As Long: x = 6

    Set WS1 = ThisWorkbook.Sheets("WB1")

    Set WS2 = Workbooks("sample_WB2.xlsx").Sheets("vulnerabilities.json-critical-o")

    Set WS3 = Workbooks("sample_WB3.xlsx").Sheets("Sheet1")

    Set WS4 = Workbooks("sample_WB4.xlsx").Sheets("Sheet1")

    Set RngList = CreateObject("Scripting.Dictionary")

    For Each Rng In WS1.Range("I2", WS1.Range("I" & Rows.Count).End(xlUp))

    If Not RngList.Exists(Rng.Value) Then

    RngList.Add Rng.Value, Nothing

    End If

    Next Rng

    For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp))

    If RngList.Exists(Rng.Value) Then

    WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x

    End If

    Next Rng

    RngList.RemoveAll

    x = x + 1

    For Each Rng In WS2.Range("F2", WS2.Range("F" & Rows.Count).End(xlUp))

    If Not RngList.Exists(Rng.Value) Then

    RngList.Add Rng.Value, Nothing

    End If

    Next Rng

    For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp))

    If RngList.Exists(Rng.Value) Then

    WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x

    End If

    Next

    RngList.RemoveAll

    x = x + 1

    For Each Rng In WS3.Range("Q2", WS3.Range("Q" & Rows.Count).End(xlUp))

    If Not RngList.Exists(Rng.Value) Then

    RngList.Add Rng.Value, Nothing

    End If

    Next Rng

    For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp))

    If RngList.Exists(Rng.Value) Then

    WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x

    End If

    Next

    RngList.RemoveAll

    x = x + 1

    For Each Rng In WS4.Range("B2", WS4.Range("B" & Rows.Count).End(xlUp))

    If Not RngList.Exists(Rng.Value) Then

    RngList.Add Rng.Value, Nothing

    End If

    Next Rng

    For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp))

    If RngList.Exists(Rng.Value) Then

    WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x

    End If

    Next

    Application.ScreenUpdating = True

    End Sub

    It worked, may I ask further help? Similar kind of comparison should be multiple sheets. WB1 is the primary sheet should be compared with WB2, WB3 & WB4. With single macro compare WB2, WB3 & WB4 one after another and highlight the matches in different color. WB2 = Yellow, WB3 = Blue, WB4 = Green. I have attached sample WB3 file, do not have WB4 file, please assume Column B in WB4 to be verified.


    Thanks for the fantastic work, it is much appreciated.

    Files

    I have two different excel file, ex WB1, WB2. Need to compare column from WB1, ex: column H to another workbook WB2 with specific column Ex: column F and highlight the difference in column H of WB1. column contains both text and numbers. I know it possible using vlookup, but need to automate this using macro, can I someone provide macro to do the same? Thanks in advance