Excel VBA需求：Sheet1到Sheet2数据更新、变更单元格高亮及新增行追加

• 需求：用VBA实现Sheet1数据更新至Sheet2，规则如下：
  • Sheet1的A列是title列，Sheet2的B列是title列，目标表为Sheet2
  • 当Sheet2的B列存在与Sheet1的A列匹配的title时，更新该行其他列数据；单元格值变更时高亮该单元格
  • Sheet1中与Sheet2不匹配的title行，追加到Sheet2末尾
• 现有问题：已实现数据更新，但不知道如何添加单元格变更高亮和不匹配行追加功能，现有代码如下：

Sub AG()
    Dim sourceWS As Worksheet, targetWS As Worksheet
    Dim lastCol As Long, LastRow As Long, srcRow As Range
    Dim found1 As Range, found2 As Range, j As Long, Cr1 As String
   
    Dim FRow As Long
    Dim i As Integer
    Dim k As Integer
    Set sourceWS = Worksheets("sheet2")
    Set targetWS = Worksheets("sheet3")
   
    LastRow = Sheets("sheet2").Range("A1").SpecialCells(xlCellTypeLastCell).Row
    FRow = Sheets("sheet3").Range("A1").SpecialCells(xlCellTypeLastCell).Row
    For i = 2 To LastRow
    For k = 2 To FRow
   
    If Worksheets("sheet2").Range("A" & i).Value = Worksheets("sheet3").Range("B" & k).Value Then
   
     With sourceWS
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        For j = 1 To lastCol
        Cr1 = .Cells(1, j).Value
        Set srcRow = .Range("A1", .Cells(1, lastCol))
        Set found1 = srcRow.Find(What:=Cr1, LookAt:=xlWhole, MatchCase:=False)

        If Not found1 Is Nothing Then
            lastCol = targetWS.Cells(1, Columns.Count).End(xlToLeft).Column
            Set srcRow = targetWS.Range("A1", targetWS.Cells(1, lastCol))
            Set found2 = srcRow.Find(What:=Cr1, LookAt:=xlWhole, MatchCase:=False)

            If Not found2 Is Nothing Then
                LastRow = .Cells(Rows.Count, found1.Column).End(xlUp).Row
                .Range(.Cells(2, found1.Column), .Cells(LastRow, found1.Column)).Copy
                found2.Offset(2, 0).PasteSpecial xlPasteAll
                rowNumberValue = ActiveCell.Row
                columnNumberValue = ActiveCell.Column
                 
            End If
        End If
      Next j
    End With
    End If
    Next
    Next
    End Sub

Sub UpdateSheet2FromSheet1()
    Dim sourceWS As Worksheet, targetWS As Worksheet
    Dim srcLastRow As Long, tgtLastRow As Long, srcLastCol As Long, tgtLastCol As Long
    Dim i As Long, j As Long, matchRow As Long
    Dim isMatched As Boolean
    Dim srcTitle As String
    Dim matchRange As Range, tgtCol As Range
    
    ' 定义源表（Sheet1）和目标表（Sheet2）
    Set sourceWS = ThisWorkbook.Worksheets("Sheet1")
    Set targetWS = ThisWorkbook.Worksheets("Sheet2")
    
    ' 获取源表和目标表的最后行、列（避免用SpecialCells导致的错误）
    srcLastRow = sourceWS.Cells(sourceWS.Rows.Count, "A").End(xlUp).Row
    srcLastCol = sourceWS.Cells(1, sourceWS.Columns.Count).End(xlToLeft).Column
    tgtLastRow = targetWS.Cells(targetWS.Rows.Count, "B").End(xlUp).Row
    
    ' 1. 遍历源表每一行，处理匹配与不匹配逻辑
    For i = 2 To srcLastRow
        srcTitle = sourceWS.Cells(i, "A").Value
        isMatched = False
        
        ' 在目标表B列查找匹配的title
        Set matchRange = targetWS.Range("B:B").Find(What:=srcTitle, LookAt:=xlWhole, MatchCase:=False)
        
        If Not matchRange Is Nothing Then
            matchRow = matchRange.Row
            isMatched = True
            
            ' 遍历源表当前行的所有列，按表头匹配更新目标表数据
            For j = 1 To srcLastCol
                Set tgtCol = targetWS.Rows(1).Find(What:=sourceWS.Cells(1, j).Value, LookAt:=xlWhole, MatchCase:=False)
                
                If Not tgtCol Is Nothing Then
                    ' 值不同则更新并高亮单元格（示例用黄色填充）
                    If targetWS.Cells(matchRow, tgtCol.Column).Value <> sourceWS.Cells(i, j).Value Then
                        targetWS.Cells(matchRow, tgtCol.Column).Value = sourceWS.Cells(i, j).Value
                        targetWS.Cells(matchRow, tgtCol.Column).Interior.Color = RGB(255, 255, 0)
                    End If
                End If
            Next j
        End If
        
        ' 2. 未匹配的行追加到目标表末尾
        If Not isMatched Then
            tgtLastRow = tgtLastRow + 1
            ' 复制源表当前行数据到目标表新行
            sourceWS.Rows(i).Copy Destination:=targetWS.Rows(tgtLastRow)
            ' 若需要调整列对应关系，可替换为逐个单元格赋值，例如：
            ' targetWS.Cells(tgtLastRow, "B").Value = srcTitle
            ' targetWS.Cells(tgtLastRow, "C").Value = sourceWS.Cells(i, "C").Value
        End If
    Next i
    
    ' 清除剪贴板状态，避免弹窗提示
    Application.CutCopyMode = False
    MsgBox "数据更新完成！", vbInformation
End Sub

• 修正表名映射：把原代码的表名错误（Sheet2当源表、Sheet3当目标表）调整为符合需求的Sheet1（源）和Sheet2（目标）
• 单元格变更高亮：通过对比源表和目标表的单元格值，当值不同时更新并设置单元格填充色（可修改RGB值调整高亮颜色）
• 不匹配行追加：遍历源表时标记每行是否匹配，未匹配则将整行复制到目标表最后一行，同步更新目标表最后行号
• 优化查找逻辑：用Find方法替代双层循环，提升运行效率；按表头匹配列，确保列对应关系正确
• 避免错误：用Cells(Rows.Count, 列).End(xlUp).Row获取最后行，避免SpecialCells可能出现的空表错误

内容的提问来源于stack exchange，提问作者AG3