vba 为行着色的编程宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7473804/
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
Programming macro to color rows
提问by Flethuseo
I am trying to program a macro that colors the rows where it finds a 0 value in the current column. I am unsure how to fix it though because it only does the first Range selection to the right ignoring the rest.
我正在尝试编写一个宏，该宏为在当前列中找到 0 值的行着色。我不确定如何修复它，因为它只在右边执行第一个范围选择而忽略其余部分。
How can I tell the command to select all cells to the right of the current one up to Column T?
如何告诉命令选择当前单元格右侧的所有单元格，直到第 T 列？
Sub FindAndColor()
'
' FindAndColor Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Cells.Find(What:="0", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Style = "Bad"
End Sub
Also, I would like it to put me in the cell below the current one? What needs to go in place of "A1" so that it is the current cell and not A1.
另外，我希望它把我放在当前单元格下面的单元格中吗？需要什么来代替“A1”，以便它是当前单元格而不是 A1。
Range("A1").Offset(0,1)
Any help appreciated,
任何帮助表示赞赏，
Ted
泰德
回答by Banjoe
Are you trying to loop through all the rows of the sheet and change the formatting of any row where the currently selected column is 0? If so, the below will work without using .Select (.Select is evil).
您是否尝试遍历工作表的所有行并更改当前所选列为 0 的任何行的格式？如果是这样，下面将在不使用 .Select 的情况下工作（.Select 是邪恶的）。
Sub FindAndColor2()
'This code will find each row with 0 in the currently selected column
' and color the row from column 1 to 20 (A to T) red and set the
' font to bold.
Dim row As Long
Dim col As Long
'Get the column of the current selection
col = Selection.Column
'Loop through every row in the active worksheet
For row = 1 To ActiveSheet.UsedRange.Rows.Count
'If the cell's value is 0.
If ActiveSheet.Cells(row, col).Text = 0 Then
'Put your formatting inside this with. Note that the 20 in .cells(row, 20)
' is column T.
With ActiveSheet.Range(ActiveSheet.Cells(row, 1), ActiveSheet.Cells(row, 20))
.Interior.Color = vbRed
.Font.Bold = True
.Style = "Bad"
'Other formatting here
End With
End If
Next row
End Sub
回答by Tipx
I know you asked something about VBA, but in a case like this, you might want to consider using conditional formating. If it's not applicable, you can go with
我知道您询问了有关 VBA 的问题，但在这种情况下，您可能需要考虑使用条件格式。如果它不适用，你可以去
Range([Fill Me]).Interior.Color = vbRed
or
或者
Cells([Fill Me]).Interior.Color = vbRed
