Welcome to FreeSoftwareServers Confluence Wiki

Public Function CompareColumns(ColA As String, ColB As String, ws As Worksheet) As Boolean

ws.Activate

lRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

ws.Range(ColA & "1:" & ColA & lRow).Sort key1:=Range(ColA & "1"), Order1:=xlAscending, Header:=xlYes
ws.Range(ColB & "1:" & ColB & lRow).Sort key1:=Range(ColB & "1"), Order1:=xlAscending, Header:=xlYes
      
Dim rcell As Range, rng As Range

Set rng = ws.Range(ColALetter & "2:" & ColALetter & lRow)

For Each rcell In rng.Cells
 If Not IsError(rcell.Value) Then
  If rcell.Value <> "" Then
   If InStr(rcell.Value, Cells(rcell.Row, ColB).Value) = 0 Then
    MsgBox "Comparision Failed at " & rcell.Address & "Please Investigate!"
    Range(rcell.Address).Interior.ColorIndex = 3
    Range(rcell.Address).Select
    CompareColumns = True
    Exit For
   End If
  End If
 End If
Next rcell

End Function
Public Sub TestCompare()

Dim CompCols As Boolean
CompCols = CompareColumns("B", "C", ActiveSheet)
If CompCols = False Then
 Debug.Print "Comparison Passed"
Else
 Debug.Print "Comparison Failed"
End If
End Sub
  • No labels