Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata
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