时间戳

首页 > 解决方案 > 具有合并单元格的 VBA 格式化表

excel - 具有合并单元格的 VBA 格式化表

问题描述

如果整个范围具有相同的值，我有一个合并表格中的单元格的函数（例如，如果 A1:G1 等于 A2:B2 它将合并单元格，如 A1&A2、B1&B2 等。更多信息：如何检查两个范围值相等）现在我想更改由该函数创建的表格的颜色，例如填充颜色的第一行（无论是否合并），第二个空白等，但我不知道是否应该给它上色使用合并功能或创建另一个将检测具有合并行的新表作为一个等。下面是我的代码：

Sub test()

    Dim i As Long, j As Long, k As Long, row As Long
    row = Cells(Rows.Count, 2).End(xlUp).row
    k = 1
    For i = 1 To row Step 1
        If Cells(i, 1).Value = "" Then Exit For
        If Join(Application.Transpose(Application.Transpose(Range(Cells(i, 1), Cells(i, 3)))), Chr(0)) <> Join(Application.Transpose(Application.Transpose(Range(Cells(i + 1, 1), Cells(i + 1, 3)))), Chr(0)) Then
          If i <> k Then
            For j = 1 To 3 Step 1
                  Application.DisplayAlerts = False
                  Range(Cells(i, j), Cells(k, j)).Merge
                  Application.DisplayAlerts = True
            Next j
          End If
        k = i + 1
        End If
    Next i
End Sub

标签： excelvbamerge

解决方案

所以，经过一段时间我自己想通了。下面是代码：

Dim i As Long, j As Long, k As Long, l As Long, c As Integer
row = Cells(Rows.Count, 2).End(xlUp).row
k = 7
c = 1
For i = 7 To row Step 1
    If Join(Application.Transpose(Application.Transpose(Range(Cells(i, 1), Cells(i, 3)))), Chr(0)) <> Join(Application.Transpose(Application.Transpose(Range(Cells(i + 1, 1), Cells(i + 1, 3)))), Chr(0)) Then
      If i <> k Then
        For j = 1 To 3 Step 1
              Application.DisplayAlerts = False
              Range(Cells(i, j), Cells(k, j)).Merge
              Application.DisplayAlerts = True
        Next j
      End If
    Select Case c
        Case 0
            Range(Cells(k, 1), Cells(k, 3)).Interior.Color = xlNone
            c = 1
        Case 1
            For l = 0 To i - k Step 1
                Range(Cells(k + l, 1), Cells(k + l, 3)).Interior.Color = RGB(217, 225, 242)
            Next l
            c = 0
    End Select
    k = i + 1
    End If
Next i

推荐阅读