使用 VBA 进行颜色填充，以单元格值为条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14582498/
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
Colour Filling using VBA, Conditional on cell value
提问by Clatty Cake
I am looking to colour fill a range of cells (C2:C7) in Excel based on the the text selected in a cell (there will be a drop-down menu, two of the values being "SD" and "CS"). Here is my code so far:
我希望根据单元格中选择的文本在 Excel 中对一系列单元格 (C2:C7) 进行颜色填充（将有一个下拉菜单，其中两个值为“SD”和“CS”）。到目前为止，这是我的代码：
Sub ChangeColor() Dim rCell As Range
Sub ChangeColor() 将 rCell 调暗为范围
With Sheet1
For Each rCell In .Range("C2:C7")
If rCell.Value <= SD Then
rCell.Interior.Color = vbRed
ElseIf rCell.Value <= CS Then
rCell.Interior.Color = vbGreen
Else: rCell.Interior.Color = vbYellow
End If
End With
End Sub
I'm told there is a compile error: End With without With
我被告知有一个编译错误：End With without With
I'm just getting started in VBA so can somebody please tell me what I've done wrong?
我刚刚开始使用 VBA，所以有人可以告诉我我做错了什么吗？
回答by Peter Albert
To fix your code, you need to insert a Nextstatement. before the End Withline. Also, it is good practice to use indentation to visually see the different code blocks. In your case, it would look like this:
要修复您的代码，您需要插入一条Next语句。End With行前 。此外，最好使用缩进来直观地查看不同的代码块。在您的情况下，它看起来像这样：
Sub YourSub
With Sheet1
For Each rCell In .Range("C2:C7")
If rCell.Value <= SD Then
rCell.Interior.Color = vbRed
ElseIf rCell.Value <= CS Then
rCell.Interior.Color = vbGreen
Else
rCell.Interior.Color = vbYellow
End If
Next rCell
End With
End Sub
Also, consider to use Conditional Formatting instead of VBA here - it will do the same, but much easier!
另外，考虑在这里使用条件格式而不是 VBA - 它会做同样的事情，但更容易！
回答by Artis Davis
I'm not sure if it is working but you can try this, because when you type in script "For commands" then you need to type next. And "For" is more used to create loops:
我不确定它是否有效，但你可以试试这个，因为当你输入脚本“For commands”时，你需要输入 next。“For”更多地用于创建循环：
Sub ChangeColor()
Dim rCell As Range
With Sheet1
For Each rCell In .Range("C2:C7")
If rCell.Value <= SD Then
rCell.Interior.Color = vbRed
ElseIf rCell.Value <= CS Then
rCell.Interior.Color = vbGreen
Else rCell.Interior.Color = vbYellow
End If
Next
End With
End Sub
