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
