Excel InStr函数方向相反

Question

我有这个VBA代码，我正在使用它来比较Excel电子表格中的两列，即B列和A列。然后它会"突出显示"A列和B列中缺少的列。

我想不出的是如何颠倒搜索B列的过程，并突出显示A列中不同的列。

原始代码：

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

我尝试过重命名字母和切换列值，但我意识到它使用的是原始搜索中的值，而只是突出显示A列中的相应单元格。

Answer 1

回答您的问题：

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

如果你想使用使颜色变化实时的条件格式，你可以将两个循环都替换为：

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

编辑问题是列A中的数据在末尾有一个额外的空间，因此使instr返回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

通过修剪这些值，instr返回true。

Answer 2

有很多方法可以实现这一点。你可以使用公式，你可以创建字典。

快速解决方案是：

    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