vba 在 Excel 中交替着色行组

Alternating coloring groups of rows in Excel

提问by Marijn Deé

I have an Excel Spreadsheet like this

我有一个这样的 Excel 电子表格

id | data for id
   | more data for id
id | data for id
id | data for id
   | more data for id
   | even more data for id
id | data for id
   | more data for id
id | data for id
id | data for id
   | more data for id

Now I want to group the data of one id by alternating the background color of the rows

现在我想通过交替行的背景颜色来对一个 id 的数据进行分组

var color = white
for each row
    if the first cell is not empty and color is white
        set color to green
    if the first cell is not empty and color is green
        set color to white
    set background of row to color

Can anyone help me with a macro or some VBA code

谁能帮我一个宏或一些 VBA 代码

Thanks

谢谢

采纳答案by Jason Z

I think this does what you are looking for. Flips color when the cell in column A changes value. Runs until there is no value in column B.

我认为这可以满足您的要求。当 A 列中的单元格更改值时翻转颜色。运行直到 B 列中没有值。

Public Sub HighLightRows()
    Dim i As Integer
    i = 1
    Dim c As Integer
    c = 3       'red

    Do While (Cells(i, 2) <> "")
        If (Cells(i, 1) <> "") Then    'check for new ID
            If c = 3 Then
                c = 4   'green
            Else
                c = 3   'red
            End If
        End If

        Rows(Trim(Str(i)) + ":" + Trim(Str(i))).Interior.ColorIndex = c
        i = i + 1
    Loop
End Sub

回答by Adriano P

I use this formula to get the input for a conditional formatting:

我使用这个公式来获取条件格式的输入：

=IF(B2=B1,E1,1-E1))    [content of cell E2]

Where column B contains the item that needs to be grouped and E is an auxiliary column. Every time that the upper cell (B1 on this case) is the same as the current one (B2), the upper row content from column E is returned. Otherwise, it will return 1 minus that content (that is, the outupt will be 0 or 1, depending on the value of the upper cell).

其中 B 列包含需要分组的项目，E 为辅助列。每次上单元格（在这种情况下为 B1）与当前单元格（B2）相同时，将返回 E 列的上一行内容。否则，它将返回 1 减去该内容（即，输出将为 0 或 1，具体取决于上单元格的值）。

回答by Laurent S.

Based on Jason Z's answer, which from my tests seems to be wrong (at least on Excel 2010), here's a bit of code that happens to work for me :

根据 Jason Z 的回答，从我的测试来看似乎是错误的（至少在 Excel 2010 上），这里有一些代码恰好适用于我：

Public Sub HighLightRows()
    Dim i As Integer
    i = 2 'start at 2, cause there's nothing to compare the first row with
    Dim c As Integer
    c = 2       'Color 1. Check http://dmcritchie.mvps.org/excel/colors.htm for color indexes

    Do While (Cells(i, 1) <> "")
        If (Cells(i, 1) <> Cells(i - 1, 1)) Then 'check for different value in cell A (index=1)
            If c = 2 Then
                c = 34   'color 2
            Else
                c = 2   'color 1
            End If
        End If

        Rows(Trim(Str(i)) + ":" + Trim(Str(i))).Interior.ColorIndex = c
        i = i + 1
    Loop
End Sub

回答by KyleF

I'm barrowing this and tried to modify it for my use. I have order numbers in column a and some orders take multiple rows. Just want to alternate the white and gray per order number. What I have here alternates each row.

我正在使用它并试图修改它以供我使用。我在 a 列中有订单号，有些订单需要多行。只想根据订单号交替使用白色和灰色。我在这里的每行交替。

ChangeBackgroundColor() ' ChangeBackgroundColor Macro ' ' Keyboard Shortcut: Ctrl+Shift+B Dim a As Integer a = 1 Dim c As Integer c = 15 'gray Do While (Cells(a, 2) <> "") If (Cells(a, 1) <> "") Then 'check for new ID If c = 15 Then c = 2 'white Else c = 15 'gray End If End If Rows(Trim(Str(a)) + ":" + Trim(Str(a))).Interior.ColorIndex = c a = a + 1 Loop

ChangeBackgroundColor() ' ChangeBackgroundColor Macro ' ' Keyboard Shortcut: Ctrl+Shift+B Dim a As Integer a = 1 Dim c As Integer c = 15 'gray Do While (Cells(a, 2) <> "") If (Cells(a, 1) <> "") Then 'check for new ID If c = 15 Then c = 2 'white Else c = 15 'gray End If End If Rows(Trim(Str(a)) + ":" + Trim(Str(a))).Interior.ColorIndex = c a = a + 1 Loop

End Sub

End Sub

回答by csmba

Do you have to use code? if the table is static, then why not use the auto formatting capability?

你必须使用代码吗？如果表格是静态的，那么为什么不使用自动格式化功能呢？

It may also help if you "merge cells" of the same data. so maybe if you merge the cells of the "data, more data, even more data" into one cell, you can more easily deal with classic "each row is a row" case.

如果您“合并单元格”相同数据，它也可能有所帮助。所以也许如果你将“数据，更多数据，甚至更多数据”的单元格合并到一个单元格中，你可以更轻松地处理经典的“每一行就是一行”的情况。

回答by Daniel Pollard

If you select the Conditional Formatting menu option under the Format menu item, you will be given a dialog that lets you construct some logic to apply to that cell.

如果您选择“格式”菜单项下的“条件格式”菜单选项，您将看到一个对话框，让您可以构建一些应用于该单元格的逻辑。

Your logic might not be the same as your code above, it might look more like:

您的逻辑可能与上面的代码不同，它可能看起来更像是：

Cell Value is | equal to | | and | White .... Then choose the color.

单元格值为 | 等于 | | 和| 白色....然后选择颜色。

You can select the add button and make the condition as large as you need.

您可以选择添加按钮并根据需要使条件变大。

回答by AjV Jsy

I have reworked Bartdude's answer, for Light Grey / White based upon a configurable column, using RGB values. A boolean var is flipped when the value changes and this is used to index the colours array via the integer values of True and False. Works for me on 2010. Call the sub with the sheet number.

我已经使用 RGB 值重新设计了 Bartdude 的答案，针对基于可配置列的浅灰色/白色。当值改变时，布尔变量被翻转，这用于通过 True 和 False 的整数值索引颜色数组。在 2010 年对我来说有效。使用工作表编号调用子程序。

Public Sub HighLightRows(intSheet As Integer)
    Dim intRow As Integer: intRow = 2 ' start at 2, cause there's nothing to compare the first row with
    Dim intCol As Integer: intCol = 1 ' define the column with changing values
    Dim Colr1 As Boolean: Colr1 = True ' Will flip True/False; adding 2 gives 1 or 2
    Dim lngColors(2 + True To 2 + False) As Long   ' Indexes : 1 and 2
          ' True = -1, array index 1.    False = 0, array index 2.
    lngColors(2 + False) = RGB(235, 235, 235) ' lngColors(2) = light grey
    lngColors(2 + True) = RGB(255, 255, 255) '  lngColors(1) = white

    Do While (Sheets(intSheet).Cells(intRow, 1) <> "")
        'check for different value in intCol, flip the boolean if it's different
        If (Sheets(intSheet).Cells(intRow, intCol) <> Sheets(intSheet).Cells(intRow - 1, intCol)) Then Colr1 = Not Colr1
        Sheets(intSheet).Rows(intRow).Interior.Color = lngColors(2 + Colr1) ' one colour or the other
        ' Optional : retain borders (these no longer show through when interior colour is changed) by specifically setting them
        With Sheets(intSheet).Rows(intRow).Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = RGB(220, 220, 220)
        End With
        intRow = intRow + 1
    Loop
End Sub

Optional bonus : for SQL data, colour any NULL values with the same yellow as used in SSMS

可选奖励：对于 SQL 数据，使用与 SSMS 中使用的相同的黄色为任何 NULL 值着色

Public Sub HighLightNULLs(intSheet As Integer)
    Dim intRow As Integer: intRow = 2 ' start at 2 to avoid the headings
    Dim intCol As Integer
    Dim lngColor As Long: lngColor = RGB(255, 255, 225) ' pale yellow

    For intRow = intRow To Sheets(intSheet).UsedRange.Rows.Count
        For intCol = 1 To Sheets(intSheet).UsedRange.Columns.Count
            If Sheets(intSheet).Cells(intRow, intCol) = "NULL" Then Sheets(intSheet).Cells(intRow, intCol).Interior.Color = lngColor
        Next intCol
    Next intRow
End Sub

回答by GONeale

I use this rule in Excel to format alternating rows:

我在 Excel 中使用此规则来格式化交替行：

1. Highlight the rows you wish to apply an alternating style to.
2. Press "Conditional Formatting" -> New Rule
3. Select "Use a formula to determine which cells to format" (last entry)
4. Enter rule in format value: =MOD(ROW(),2)=0
5. Press "Format", make required formatting for alternating rows, eg. Fill -> Color.
6. Press OK, Press OK.

1. 突出显示要应用交替样式的行。
2. 按“条件格式”-> 新规则
3. 选择“使用公式确定要设置格式的单元格”（最后一个条目）
4. 在格式值中输入规则： =MOD(ROW(),2)=0
5. 按“格式”，为交替行进行所需的格式设置，例如。填充 -> 颜色。
6. 按确定，按确定。

If you wish to format alternating columns instead, use =MOD(COLUMN(),2)=0

如果您希望格式化交替列，请使用 =MOD(COLUMN(),2)=0

Voila!

瞧！