Excel : Compare Data in ColA with ColB In excel (Non sequential)

 Compare Data in ColA with ColB In excel (Non sequential)

Ref : How To Enable VBA to Enable VBA in Excel

Sub Deepak_compare_c1_c2()

'This code compares the values in ColA of sheet 1 with ColB in the same sheet

'ColA = Expected Value

'ColB = Actual Value

'Values in Col A can be anywhere in ColB it will be searched

'If values in the ColA is repeated Eg {1,4,4} then it will search for 2 repeated values in ColB 'Eg:{5,6,1,4,7,4} - here 1,4,4 all three are found

Dim row_count, sExp_val, bFlag, ilast_row_ColA, ilast_row_ColB

With ActiveSheet

ilast_row_ColA = .Cells(.rows.Count, "A").End(xlUp).Row ' Last used Row in ColA

End With

With ActiveSheet

ilast_row_ColB = .Cells(.rows.Count, "B").End(xlUp).Row ' last used Row in ColB

End With

MsgBox ("ColA =" & ilast_row_ColA & " ColB =" & ilast_row_ColB)

For i = 1 To ilast_row_ColA

bFlag = False

sExp_val = Cells(i, 1).Value

For j = 1 To ilast_row_ColB

If (sExp_val = Cells(j, 2).Value And Cells(j, 2).Interior.ColorIndex <> 15) Then

Cells(i, 1).Interior.Color = vbGreen

Cells(j, 2).Interior.ColorIndex = 15

bFlag = True

End If

If (bFlag = True) Then

Exit For

End If

Next

If (bFlag = False) Then

Cells(i, 1).Interior.Color = vbRed

End If

Next

MsgBox ("Done" & vbNewLine & "Red = not Found" & vbNewLine & "Green=Found" & vbNewLine & "Grey=Greyed Out once it is matched")

End Sub