如何使用 Excel VBA 在一行中循环遍历 5 个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17154744/
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
How to Loop Through 5 Cells in a Row Using Excel VBA
提问by blahblahblahblah
I want to loop through 5 cells, Q5 - U5.
我想遍历 5 个单元格，Q5 - U5。
With each cell I want to check if the value is equal to "Y", and if yes, highlight the cell to make it green.
对于每个单元格，我想检查该值是否等于“Y”，如果是，则突出显示该单元格以使其变为绿色。
How may I do so? Can't seem to figure it out.
我怎么能这样做？好像想不通
For Each c In Range("Q5:U5").Cells
c.Select
If c.Value = Y Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next
回答by Tim Williams
You should try to avoid selecting/activating ranges: in 99% of cases there is no need (although the macro recorder always suggests otherwise)
您应该尽量避免选择/激活范围：在 99% 的情况下没有必要（尽管宏记录器总是建议否则）
For Each c In ActiveSheet.Range("Q5:U5").Cells
If c.Value = "Y" Then
With c.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next
回答by Floris
When you don't define c as a range, the statement
当您不将 c 定义为范围时，语句
For Each c in ActiveSheet.Range("Q5:U5").Cells
while valid, will actually result in chaving the value of each of the cells. To solve this problem, declare the type explicitly:
虽然有效，但实际上会产生c每个单元格的值。要解决此问题，请显式声明类型：
Dim c as Range
Next, when you do the comparison (as already pointed out), use
接下来，当您进行比较时（如已经指出的那样），请使用
If c.Value = "Y"
Note - if you declare
注意 - 如果您声明
Option Compare Text
right at the top of your module, the comparison will be case-insensitive; otherwise, a "Y" will not match a "y".
在模块的顶部，比较将不区分大小写；否则，“Y”将不匹配“y”。
The whole module would look like this, then:
整个模块看起来像这样，然后：
Option Explicit
Option Compare Text
Sub colorMe()
Dim c as Range
For Each c In Range("Q5:U5").Cells
c.Select
If c.Value = "Y" Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next
End Sub
I am sure it doesn't need to be pointed out that you could achieve the same thing with conditional formatting...
我相信不需要指出你可以用条件格式来实现同样的事情......
回答by mr.Reband
In your code, Yappears to be an undefined variable. To check for the value, put it in double quotes:
在您的代码中，Y似乎是一个未定义的变量。要检查该值，请将其放在双引号中：
If c.Value = "Y" Then
If c.Value = "Y" Then
