Compare Two Columns In Different Worksheets & Add Found Results

VBA Worksheet Function : CountIf


           Two columns in different worksheets were compared in this template.That is, column A of Page1 compared with column A of Page2.
The found different results as entire row were copied to second worksheet (Page2). 

✔️ Also new row or rows were highlighted .

excel vba compare two columns

Our VBA codes:
Sub compare_columns()
Dim stk, msb As Worksheet
Set stk = Sheets("Page1")
Set msb = Sheets("Page2")

Application.ScreenUpdating = False
sat = (msb.Range("A" & Rows.Count).End(xlUp).Row) + 1
For i = 2 To stk.Range("A" & Rows.Count).End(xlUp).Row
    If WorksheetFunction.CountIf(msb.Range("A2:A" & msb.Range("A" & Rows.Count).End(xlUp).Row), stk.Cells(i, "A")) = 0 Then
        msb.Range("a" & sat).EntireRow.Value = stk.Range("a" & i).EntireRow.Value
        msb.Range("a" & sat).Interior.ColorIndex = 22
        sat = sat + 1
    End If
Next
Application.ScreenUpdating = True
Set stk = Nothing: Set msb = Nothing
End Sub


1 comment:

  1. Hi. I try the VBA codes. so far, after it detect column not same, then it just copy 1 row only. not copying below row of it. please show how to loop until end of data.

    ReplyDelete