vba 范围内的交替行颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4629100/
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
Alternate Row Colors in Range
提问by Kurt
I've come up with the following to alternate row colors within a specified range:
我想出了以下在指定范围内交替行颜色的方法：
Sub AlternateRowColors()
Dim lastRow as Long
lastRow = Range("A1").End(xlDown).Row
For Each Cell In Range("A1:A" & lastRow) ''change range accordingly
If Cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
Cell.Interior.ColorIndex = 15 ''color to preference
Else
Cell.Interior.ColorIndex = xlNone ''color to preference or remove
End If
Next Cell
End Sub
That works, but is there a simpler method?
那行得通，但有没有更简单的方法？
The following lines of code may be removed if your data contains no pre-exisiting colors:
如果您的数据不包含预先存在的颜色，则可能会删除以下代码行：
Else
Cell.Interior.ColorIndex = xlNone
采纳答案by e.James
Alternating row colors can be done using conditional formatting:
可以使用条件格式来完成交替行颜色：
回答by Jon Crowell
I need to do this frequently and like to be able to easily modify the colors I'm using for the banding. The following sub makes it very easy:
我需要经常这样做，并且希望能够轻松修改我用于条带的颜色。以下子项使其变得非常容易：
Sub GreenBarMe(rng As Range, firstColor As Long, secondColor As Long)
rng.Interior.ColorIndex = xlNone
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
rng.FormatConditions(1).Interior.Color = firstColor
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)<>0"
rng.FormatConditions(2).Interior.Color = secondColor
End Sub
Usage:
用法：
Sub TestGreenBarFormatting()
Dim rng As Range
Dim firstColor As Long
Dim secondColor As Long
Set rng = Range("A1:D12")
firstColor = vbGreen
secondColor = vbYellow
Call GreenBarMe(rng, firstColor, secondColor)
End Sub
回答by Geko
I needed a macro that would color every second row in a range, using only those rows that were visible. This is what I came up with. You don't have to loop through the rows.
我需要一个宏来为范围内的每一行着色，只使用那些可见的行。这就是我想出的。您不必遍历行。
Sub Color_Alt_Rows(Rng As Range)
Application.ScreenUpdating = False
Rng.Interior.ColorIndex = xlNone
Rng = Rng.SpecialCells(xlCellTypeVisible)
Rng.FormatConditions.Add Type:=xlExpression, Formula1:="=mod(row()+1,2)"
Rng.FormatConditions(1).Interior.ColorIndex = 34
End Sub
Try it out with Color_Alt_Rows Range("a2:d5")
试试看 Color_Alt_Rows Range("a2:d5")
回答by Mak
My Solution
我的解决方案
A subroutine to assign to a button or some code
分配给按钮或某些代码的子程序
Public Sub Band_Goals()
'Just pass the start and end rows
'You will have to update the function to select the
'the correct columns
BandRows_Invisble 12, 144
End Sub
The Function
功能
Private Sub BandRows_Invisble(StartRow As Integer, EndRow As Integer)
Dim i As Long, nothidden As Boolean
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A" & StartRow & ":K" & EndRow).Interior.ColorIndex = xlNone
For i = StartRow To EndRow
If Not Rows(i).Hidden Then
nothidden = nothidden + 1
If Not nothidden Then
'Download this app to help with color picking
'http://www.iconico.com/download.aspx?app=ColorPic
Range("A" & i & ":K" & i).Interior.Color = RGB(196, 189, 151)
End If
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
回答by nick316igr
'--- Alternate Row color, only non-hidden rows count
Sub Test()
Dim iNumOfRows As Integer, iStartFromRow As Integer, iCount As Integer
iNumOfRows = Range("D61").End(xlDown).Row '--- counts Rows down starting from D61
For iStartFromRow = 61 To iNumOfRows
If Rows(iStartFromRow).Hidden = False Then '--- only non-hidden rows matter
iCount = iCount + 1
If iCount - 2 * Int(iCount / 2) = 0 Then
Rows(iStartFromRow).Interior.Color = RGB(220, 230, 241)
Else
Rows(iStartFromRow).Interior.Color = RGB(184, 204, 228)
End If
End If
Next iStartFromRow
End Sub
回答by Bernhard
In my Excel 2010, there is an option to format as table, where you can also select a range and headers. No need for scripting.
在我的 Excel 2010 中，有一个选项可以格式化为表格，您还可以在其中选择范围和标题。无需编写脚本。
回答by Carlos Valenzuela
Well, you can delete the elsepart, since you will leave it in the default color
好吧，您可以删除该else部分，因为您将保留它的默认颜色
回答by Red_Riot
set these up initialized somewhere:
设置这些在某处初始化：
Dim arr_Lng_Row_Color(1) As Long
arr_Lng_Row_Color(0) = RGB(int_Color_1_R, int_Color_1_G, int_Color_1_B)
arr_Lng_Row_Color(1) = RGB(int_Color_2_R, int_Color_2_G, int_Color_2_B)
On any row you wish this will set the color
在您希望这将设置颜色的任何行
ws_SomeSheet.Rows(int_Target_Row).EntireRow.Interior.Color = arr_Lng_Row_Color(int_Target_Row Mod 2)
