VBA - 用于比较两列的 Excel

Question

我创建了一个 VBA，它将比较两张相同的 Excel 文件。如果工作表 A 中的数据不准确，它将将该行的颜色更改为红色，如果我的颜色发生更改，我也会应用过滤器。

现在的问题是它没有以适当的方式工作。就像我的数据相同，那么它也正在应用过滤器。

请参阅下面的代码

Sub Validate_Metadata()
Dim myRng As Range
Dim lastCell As Long
Dim flag As Boolean

    'Get the last row
    Dim lastRow As Integer
    lastRow = ActiveSheet.UsedRange.Rows.Count

    'Debug.Print "Last Row is " & lastRow

    Dim c As Range
    Dim d As Range

    Application.ScreenUpdating = False



    For Each c In Worksheets("Sheet1").Range("A2:A" & lastRow).Cells
        For Each d In Worksheets("Sheet2").Range("A2:A" & lastRow).Cells
            c.Interior.Color = vbRed
            flag = False
             If (InStr(1, d, c, 1) > 0) Then
                c.Interior.Color = vbWhite
                Exit For
            End If
        Next
    Next

    If (flag <> True) Then

      ActiveSheet.Range("A1:A" & lastRow).AutoFilter Field:=1, Criteria1:=RGB(255, 0 _
        , 0), Operator:=xlFilterCellColor
    End If

Application.ScreenUpdating = True
End Sub

谢谢

Answer 1

尝试这个:

Sub Validate_Metadata()
  Dim myRng As Range
  Dim lastCell As Long
  Dim flag As Boolean

  'Get the last row
  Dim lastRow As Integer
  Dim localFlag As Boolean
  lastRow = ActiveSheet.UsedRange.Rows.Count

  'Debug.Print "Last Row is " & lastRow

  Dim c As Range
  Dim d As Range

  Application.ScreenUpdating = False


  flag = True
  For Each c In Worksheets("Sheet1").Range("A2:A" & lastRow).Cells
    localFlag = False
    For Each d In Worksheets("Sheet2").Range("A2:A" & lastRow).Cells
        c.Interior.Color = vbRed
         If (InStr(1, d, c, 1) > 0) Then
            c.Interior.Color = vbWhite
            localFlag = True
            Exit For
        End If
    Next
    flag = flag And localFlag
  Next

  If (flag <> True) Then

    ActiveSheet.Range("A1:A" & lastRow).AutoFilter Field:=1, 
    Criteria1:=RGB(255, 0 _
    , 0), Operator:=xlFilterCellColor
  End If

   Application.ScreenUpdating = True
 End Sub