通过VBA实现Excel单元格按值及格式对齐的需求与代码实现

需求概述 #

• 匹配A列与E列的单元格值，调整行位置使对应值处于同一行，同时同步移动B列和F列的对应内容
• 若某列的值在另一列无匹配项，对应行的另一列单元格留空
• 仅处理字体颜色为黑色（颜色值为0）的单元格，确保同色单元格对齐到同一行

VBA实现代码 #

Sub AlignMatches()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim matchRowA As Range, matchRowE As Range
    Dim matchFound As Boolean

    ' 指定工作表
    Set ws = ThisWorkbook.Worksheets("TEST")
    
    ' 获取最后一行行号
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 从第一行循环至最后一行
    For i = 1 To lastRow
        matchFound = False
        
        ' 若A列和E列单元格字体颜色为0且值不匹配
        If ws.Cells(i, 1).Font.Color = 0 And ws.Cells(i, 5).Font.Color = 0 _
        And ws.Cells(i, 1).Value <> ws.Cells(i, 5).Value Then

            ' 在A列中查找与E列当前单元格匹配的值
            Set matchRowE = ws.Range("A:A").Find(ws.Cells(i, 5).Value, , xlValues, xlWhole)
            
            ' 若在A列找到匹配项
            If Not matchRowE Is Nothing Then
                If matchRowE.Font.Color = 0 Then
                    ' 将E列和F列的值移动到匹配行
                    ws.Cells(matchRowE.Row, 5).Value = ws.Cells(i, 5).Value
                    ws.Cells(matchRowE.Row, 6).Value = ws.Cells(i, 6).Value
                    ws.Cells(i, 5).ClearContents
                    ws.Cells(i, 6).ClearContents
                    matchFound = True
                End If
            End If
            
            ' 若未找到匹配项，在E列中查找与A列当前单元格匹配的值
            If Not matchFound Then
                Set matchRowA = ws.Range("E:E").Find(ws.Cells(i, 1).Value, , xlValues, xlWhole)
                If Not matchRowA Is Nothing Then
                    If matchRowA.Font.Color = 0 Then
                        ' 将A列和B列的值移动到匹配行
                        ws.Cells(matchRowA.Row, 1).Value = ws.Cells(i, 1).Value
                        ws.Cells(matchRowA.Row, 2).Value = ws.Cells(i, 2).Value
                        ws.Cells(i, 1).ClearContents
                        ws.Cells(i, 2).ClearContents
                    End If
                End If
            End If
        End If
    Next i
End Sub

使用提示 #

1. 确保目标工作表名称为TEST，如需修改，调整代码中Set ws = ThisWorkbook.Worksheets("TEST")的工作表名称即可
2. 代码默认处理字体颜色为黑色（颜色值为0）的单元格，若需适配其他颜色，修改Font.Color = 0的判断条件
3. 运行前建议备份数据，避免数据丢失

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