当 A 列中的值发生变化时，用于更改行颜色的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4257730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA code to change row color when value in column A changes
提问by Hyman
My spreadsheet is set up like this:
我的电子表格设置如下：
I have a list of job numbers in column A and a corresponding list of Job titles in Column B. I need for all of the rows of a certain job number to be colored. e.g., rows 2-4 have "3705" in column A, so those rows would be colored red. Rows 5 & 6 have 4169 in column A and would be colored green. Rows 7-10 have 5518 in column A and would be colored red (alternate between red and green).
我在 A 列中有一个职位编号列表，在 B 列中有一个相应的职位名称列表。我需要为某个职位编号的所有行着色。例如，第 2-4 行在 A 列中有“3705”，因此这些行将被涂成红色。第 5 行和第 6 行在 A 列中有 4169 行，将显示为绿色。第 7-10 行在 A 列中有 5518 并且将被着色为红色（在红色和绿色之间交替）。
Can someone give me a VBA code that will do what I described above automatically/using a macro?
有人可以给我一个 VBA 代码，它会自动/使用宏执行我上面描述的操作吗？
Thanks!!
谢谢！！
回答by Vicky
Sub colorize()
Dim r As Long, val As Long, c As Long
r = 1
val = ActiveSheet.Cells(r, 1).Value
c = 4 '4 is green, 3 is red '
For r = 1 To ActiveSheet.Rows.Count
If IsEmpty(ActiveSheet.Cells(r, 1).Value) Then
Exit For
End If
If ActiveSheet.Cells(r, 1).Value <> val Then
If c = 3 Then
c = 4
Else
c = 3
End If
End If
ActiveSheet.Rows(r).Select
With Selection.Interior
.ColorIndex = c
.Pattern = xlSolid
End With
val = ActiveSheet.Cells(r, 1).Value
Next
End Sub
Add the following to the relevant Worksheet's module if you want the macro to fire every time a cell in column A changes value:
如果您希望每次 A 列中的单元格更改值时都触发宏，请将以下内容添加到相关工作表的模块中：
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then
Exit Sub
End If
colorize
End Sub
回答by Carl G
This can be done using only Excel formulas and conditional formatting if you can add a column. You might not be able to alternate red/green, but only a color and no color.
如果可以添加列，则可以仅使用 Excel 公式和条件格式来完成此操作。您可能无法交替使用红色/绿色，而只能交替使用颜色和颜色。
