Excel VBA按唯一ID突出显示2张纸-新行,已删除行,不匹配的单元格

嗨,我在同一文件中有两张纸-Sheet1和Sheet2。 A列将行中的两个单元格组合在一起,为每行创建一个唯一的ID。我需要遍历工作表1的每一行并突出显示以下条件:

  1. 工作表1上的行,不是工作表2-用绿色突出显示工作表1上的所有非空单元格。
  2. 在工作表2上的行,而不是工作表1-用黄色突出显示工作表2上所有非空单元格。
  3. 如果找到匹配项(无论行号如何),请仅突出显示该行中与红色不匹配的单元格列。

我有遍历工作表1上每个唯一ID行的代码,但是,当该行不匹配是因为工作表1上不存在记录时,它错误地突出显示了不是错误的错误。

Option Explicit

Sub main2()
Dim ds1 As Range, ds2 As Range, row As Range, col As Range, f As Range

Set ds1 = Worksheets("Sheet1").Range("A1").CurrentRegion '<--| change "DataSet1" to your actual "Data Set 1" sheet name
Set ds2 = Worksheets("Sheet2").Range("A1").CurrentRegion '<--| change "DataSet2" to your actual "Data Set 2" sheet name

For Each row In ds1.Columns(1).Cells '<--| loop through "Data Set 1" "UniqueID" values (in its column 1)
    Set f = ds2.Columns(1).Find(what:=row.Value, LookIn:=xlValues, lookat:=xlWhole) '<--| look for current UniqueID in "Data Set 2" column 1
    If Not f Is Nothing Then '<-- if "Data Set 1" "UniqueID" found in "Data Set 2", then...
        For Each col In ds1.Rows(row.row).Cells '<--| ...loop through "Data Set 1" "UniqueID" row cells...
            If col.Value <> ds2(col.row, col.Column) Then '<--| ... if current cell value doesn't match corresponding "Data Set 2" one, then ...
                col.Interior.Color = RGB(255, 0, 0) '<--| ...mark "Data Set 1" current cell...
                ds2(col.row, col.Column).Interior.Color = RGB(255, 0, 0) '<--| ...mark corresponding "Data Set 2" one
            End If
        Next col
    End If
Next row
End Sub

Example Output: Example Output Image - green new row on sheet 1, yellow is row that was deleted in sheet 1 and red are rows found on both sheets based on UniqueID but with highlighted changes for cell variations.. in both cases the tables are mixed order

评论
  • 十梦九你
    十梦九你 回复

    这个

    If col.Value <> ds2(col.row, col.Column) Then
    

    应该

    If col.Value <> ds2(f.row, col.Column) Then
    

    相似地

    ds2(f.row, col.Column).Interior.Color = RGB(255, 0, 0)
    

    Currently you're not using f anywhere...