基于值的颜色单元格 - VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20799880/
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
Color cells based on their value - VBA
提问by user3139095
I need to be able to colour code cells based on their values in VBA as conditional formatting will not handle the number of conditions I will ultimately require. For instance if the value of B is "Decommission" then I would like to check the values of C, D and E and colour code them in relation to the value of B. Unfortunately, the code I've written is running through the whole sheet and colour coding everything in the range based on the first value. I've defined 2 x ranges (all_data and response) I know what's wrong but I don't know how to tell the code to only restrict the formatting to the value of the row.. Any help would be greatly appreciated.
我需要能够根据 VBA 中的值对单元格进行颜色编码，因为条件格式无法处理我最终需要的条件数量。例如，如果 B 的值是“退役”，那么我想检查 C、D 和 E 的值，并根据 B 的值对它们进行颜色编码。不幸的是，我编写的代码贯穿整个根据第一个值对范围内的所有内容进行工作表和颜色编码。我已经定义了 2 x 范围（all_data 和 response）我知道出了什么问题，但我不知道如何告诉代码只将格式限制为行的值.. 任何帮助将不胜感激。
Sub Formatter()
Dim All_Data As Range
Dim Response As Range
Dim MyCell As Range
Dim MyCell2 As Range
Set All_Data = Range("All_Data")
Set Response = Range("Response")
For Each MyCell In All_Data
If MyCell.Value = "Decommission" Then
MyCell.Interior.ColorIndex = 3
For Each MyCell2 In Response
If MyCell2.Value = "Yes" Then
MyCell2.Interior.ColorIndex = 4
End If
Next
End If
Next
End Sub
采纳答案by ttaaoossuu
Try this:
尝试这个：
Sub Formatter()
Dim All_Data As Range
Dim Response As Range
Dim MyCell As Range
Dim MyCell2 As Range
Set All_Data = Range("All_Data")
Set Response = Range("Response")
For Each MyCell In All_Data
If MyCell.Value = "Decommission" Then
MyCell.Interior.ColorIndex = 3
For Each MyCell2 In Intersect(Response, ActiveSheet.Rows(MyCell.Row))
If MyCell2.Value = "Yes" Then
MyCell2.Interior.ColorIndex = 4
End If
Next
End If
Next
End Sub
