Excel InStr Function opposite direction

Question

I have this VBA code that I am using to compare two columns in my Excel spreadsheet, column B to column A. It then "highlights" the ones that are missing from column A but in column B.

What I can't figure out is how to reverse the procedure to search column B and highlight the ones in column A that are different.

Original Code:

For i = 2 To LastRow
    For j = 2 To LastRow
        If Report.Cells(i, 2).Value <> "" Then 'This will omit blank cells at the end (in the event that the column lengths are not equal.
            If InStr(1, Report.Cells(j, 1).Value, Report.Cells(i, 2).Value, vbTextCompare) > 0 Then
                Report.Cells(i, 2).Interior.Color = xlNone 'Transparent background
                Report.Cells(i, 2).Font.Color = RGB(0, 0, 0) 'Black font color
                Exit For
            Else
                Report.Cells(i, 2).Interior.Color = RGB(156, 0, 6) 'Dark red background
                Report.Cells(i, 2).Font.Color = RGB(255, 199, 206) 'Light red font color
            End If
        End If
    Next j
Next i

I have tried renaming the letters and switching the column values and got close but realized that it was using the values from the original search and just highlighting the corresponding cells in column A.

Answer 1

To answer your question:

For j = 2 To LastRow
    For i = 2 To LastRow
        If Report.Cells(j, 1).Value <> "" Then 'This will omit blank cells at the end (in the event that the column lengths are not equal.
            If InStr(1, Report.Cells(i, 2).Value, Report.Cells(j, 1).Value, vbTextCompare) > 0 Then
                Report.Cells(j, 1).Interior.Color = xlNone 'Transparent background
                Report.Cells(j, 1).Font.Color = RGB(0, 0, 0) 'Black font color
                Exit For
            Else
                Report.Cells(j, 1).Interior.Color = RGB(156, 0, 6) 'Dark red background
                Report.Cells(j, 1).Font.Color = RGB(255, 199, 206) 'Light red font color
            End If
        End If
    Next i
Next j

If you wanted to used conditional formatting which makes the color changes live you can replace both loop with:

With Report.Range("A2:A" & LastRow).FormatConditions
    .Delete
    With .Add(Type:=xlExpression, Formula1:="=And(iserror(Vlookup(A2,B:B,1,False)),A2<>"""")")
        .Font.Color = RGB(255, 199, 206)
        .Interior.Color = RGB(156, 0, 6)
    End With
End With
With Report.Range("B2:B" & LastRow).FormatConditions
    .Delete
    With .Add(Type:=xlExpression, Formula1:="=And(iserror(Vlookup(B2,A:A,1,False)),B2<>"""")")
        .Font.Color = RGB(255, 199, 206)
        .Interior.Color = RGB(156, 0, 6)
    End With
End With

Edit the issue was that the data in Column A had an extra space at the end thus making the instr to return false.

For j = 2 To LastRow
    Report.Cells(j, 1).Value = Trim(Report.Cells(j, 1).Value)
    For i = 2 To LastRow
        Report.Cells(i, 2).Value = Trim(Report.Cells(i, 2).Value)
        If Report.Cells(j, 1).Value <> "" Then 'This will omit blank cells at the end (in the event that the column lengths are not equal.
            If InStr(1, Report.Cells(i, 2).Value, Report.Cells(j, 1).Value, vbTextCompare) > 0 Then
                Report.Cells(j, 1).Interior.Color = xlNone 'Transparent background
                Report.Cells(j, 1).Font.Color = RGB(0, 0, 0) 'Black font color
                Exit For
            Else
                Report.Cells(j, 1).Interior.Color = RGB(156, 0, 6) 'Dark red background
                Report.Cells(j, 1).Font.Color = RGB(255, 199, 206) 'Light red font color
            End If
        End If
    Next i
Next j

By trimming the values, the instr returned true.

Answer 2

There are many ways to accomplish this. You could use formulas, you could create dictionaries.

A Quick solution would be:

    Dim stringCount As Integer
    Dim myString As String
    Dim col1Range As Range
    Dim col2Range As Range

    Set col1Range = Report.Range("A1")
    Set col2Range = Report.Range("B1")

    For i = 1 To LastRow
        myString = col1Range.Offset(i).Value

        If myString <> "" Then
            stringCount = WorksheetFunction.CountIf(Range("B:B"), myString)

            If (stringCount = 0) Then
                col1Range.Offset(i).Interior.Color = RGB(156, 0, 6) 'Dark red background
                col1Range.Offset(i).Font.Color = RGB(255, 199, 206) 'Light red font color
            Else
                col1Range.Offset(i).Interior.Color = xlNone 'Transparent background
                col1Range.Offset(i).Font.Color = RGB(0, 0, 0) 'Black font color
            End If
        End If
    Next i

    For j = 1 To LastRow
        myString = col2Range.Offset(j).Value

        If myString <> "" Then
            stringCount = WorksheetFunction.CountIf(Range("A:A"), myString)

            If (stringCount = 0) Then
                col2Range.Offset(j).Interior.Color = RGB(156, 0, 6) 'Dark red background
                col2Range.Offset(j).Font.Color = RGB(255, 199, 206) 'Light red font color
            Else
                col2Range.Offset(j).Interior.Color = xlNone 'Transparent background
                col2Range.Offset(j).Font.Color = RGB(0, 0, 0) 'Black font color
            End If
        End If
    Next j