2 different Ranges Compare from different Sheets not Working VBA

Question

Why isnt this text Compare working? I'm trying to compare 2 different ranges from different sheets.

Is there a good simple way of doing this?

Sub selecttest()

Text3 = Sheets("Input DATA").Range(Cells(2, 2), Cells(2, Columns.Count).End(xlToLeft))
Text4 = Sheets("SAP Output DATA").Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp))

If StrComp(Text3, Text4, vbTextCompare) = 0 Then

Else

Cells(Cell.Row, "A").Interior.ColorIndex = 26
Cells(Cell.Row, "B").Interior.ColorIndex = 26
Cells(Cell.Row, "C").Interior.ColorIndex = 26
Cells(Cell.Row, "D").Interior.ColorIndex = 26

End If
End Sub

Is there something that im doing incorrectly?

Also tried this with no errors but it wont solve:

Sub comprangetest()

With ThisWorkbook.Sheets("Input DATA")
Text3 = Range(Cells(2, 2), Cells(2, Columns.Count).End(xlToLeft)).Select
End With

With ThisWorkbook.Sheets("SAP Output DATA")
Text4 = Sheets("SAP Output DATA").Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp)).Select
End With

'Text3 = Sheets("Input DATA").Range(Cells(2, 2), Cells(2, Columns.Count).End(xlToLeft))
'Text4 = Sheets("SAP Output DATA").Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp))

If StrComp(Text3, Text4, vbTextCompare) = 0 Then

Else

ActiveSheet.Cells(Cell.Row, "A").Interior.ColorIndex = 26
ActiveSheet.Cells(Cell.Row, "B").Interior.ColorIndex = 26
ActiveSheet.Cells(Cell.Row, "C").Interior.ColorIndex = 26
ActiveSheet.Cells(Cell.Row, "D").Interior.ColorIndex = 26

End If

End Sub

Am i using the correct method?

Answer 1

Instead of comparing from different sheets, i just brought the range over to the current sheet and made it a requirement to use a selection before the compare initiates. Since the source range is on a row, i used k as the integer for where the source is. The source always changes and is always above the selection. so that line is used for the compare. Of coarse i can probably now even go further and create a selection range from another sheet. But this works for me now. I hope i saved some time for other people struggling with this as i was.

Sub CompareRanges()
application.ScreenUpdating = False
    Dim Report As Worksheet
    Dim i As Integer, j As Integer, k As Integer
    Dim lastrow As Integer
    Dim LastColumn As Integer
    Dim sht As Worksheet
    Dim cell As Range
    Dim x As Long, y As Long
    
    Set sht = ThisWorkbook.Sheets("SAP Output DATA")

    lastrow = sht.UsedRange.Rows.Count
    LastColumn = sht.UsedRange.Columns.Count
    
    'If Selection Is Nothing Then
    'MsgBox "nothing selected, please select range."
    'Else
    
    'x is the first row number of selection, y is the last.
    x = Selection.Rows(1).row
    y = Selection.Rows.Count + x - 1
    'MsgBox x & "  " & y

    'give row number of cell above selection.
    k = Selection.Rows(1).Offset(-1, 0).row
    'MsgBox k
    
    For i = x To y 'lastrow
    'For i = 3 To lastrow 'lastrow
        For j = 5 To LastColumn
            If sht.Cells(i, 1).Value <> "" Then 'This will omit blank cells at the end (in the event that the column lengths are not equal.
                'sht.cell (2, j) "k is the variable for where is the source."
                If InStr(1, sht.Cells(k, j).Value, sht.Cells(i, 1).Value, vbTextCompare) > 0 Then
                    sht.Cells(i, 1).Interior.Color = RGB(255, 255, 255) 'White background
                    sht.Cells(i, 1).Font.Color = RGB(0, 0, 0) 'Black font color
                    Exit For
                Else
                    sht.Cells(i, 1).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    sht.Cells(i, 1).Font.Color = RGB(255, 199, 206) 'Light red font color
                End If
            End If
        Next j
    Next i
    
    'End If
application.ScreenUpdating = True
End Sub