vba 基于单元格中的值的颜色单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/320797/
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 Cell Based on Value in Cell
提问by e.James
Using a macro I have consolidated info from several workbooks into one sheet in new workbook.
使用宏，我将多个工作簿中的信息合并到新工作簿中的一张纸中。
In one column I have created a named range called ColRange. That column has numbers ranging from -350 to 500.
在其中一栏中，我创建了一个名为 ColRange 的命名范围。该列的数字范围从 -350 到 500。
How do I change the color of the cells based on the value of the text in the cell.
red(0-500)
yellow(-5-0)
green(-350--5)
如何根据单元格中文本的值更改单元格的颜色。
红(0-500) 黄(-5-0) 绿(-350--5)
回答by e.James
Have a look at conditional formatting. You may not even need VBA to do this.
看看条件格式。您甚至可能不需要 VBA 来执行此操作。
That being said, the VBA code would look something like this:
话虽如此，VBA 代码看起来像这样：
Public Sub colorit()
Dim colRange As Range
Dim rowNum As Integer
Dim rnum As Integer
rnum = 20
Set colRange = Range(Cells(2, 9), Cells(rnum, 9))
For rowNum = 1 To colRange.Rows.Count
If colRange.Cells(rowNum, 1).Value <= -5 Then
colRange.Cells(rowNum, 1).Interior.Color = RGB(0, 255, 0)
ElseIf colRange.Cells(rowNum, 1).Value <= 0 Then
colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 255, 0)
ElseIf colRange.Cells(rowNum, 1).Value <= 500 Then
colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 0, 0)
End If
Next rowNum
End Sub回答by Vincent Ramdhanie
Assume that value is the number stored in the column then:
假设 value 是存储在列中的数字，然后：
If value >= 0 AND value <= 500 Then
ColRange.Interior.Color = RGB(255,0,0)
ElseIf value >= -5 Then
ColRange.Interior.Color = RGB(255,255,200)
Else
ColRange.Interior.Color = RGB(0,255,0)
End If
And assuming that values greater than 500 or less than -350 is either not possible or validated by your script. Also, your ranges overlap a bit, what color should 0 be? Red or Yellow?
并假设值大于 500 或小于 -350 要么不可能，要么由您的脚本验证。另外，你的范围有点重叠，0应该是什么颜色？红色还是黄色？
回答by KnomDeGuerre
This is in response to the original question, it is a simple modification of Vincent's response:
这是对原始问题的回应，它是对文森特回应的简单修改：
If it is a named range (using the UI: Insert, Name, Define):
如果是命名范围（使用 UI：插入、名称、定义）：
Dim c As Range
For Each c In Range("ColRange").Cells
If c.Value >= 0 And c.Value <= 500 Then
c.Interior.Color = RGB(255, 0, 0)
ElseIf c.Value >= -5 Then
c.Interior.Color = RGB(255, 255, 200)
Else
c.Interior.Color = RGB(0, 255, 0)
End If
Next c
If it is a range object, defined in the code:
如果是range对象，在代码中定义：
Dim c as Range
For Each c In colRange.Cells
If c.Value >= 0 And c.Value <= 500 Then
c.Interior.Color = RGB(255, 0, 0)
ElseIf c.Value >= -5 Then
c.Interior.Color = RGB(255, 255, 200)
Else
c.Interior.Color = RGB(0, 255, 0)
End If
Next c
I think Vincent's response won't quite work because, it attempts to operate on the entire ColRange range, inside the If Then, rather than operating on each cell one at a time. (For this reason, you may also want to wrap it with Application.ScreenUpdating = False
我认为 Vincent 的响应不会完全奏效，因为它尝试在 If Then 内对整个 ColRange 范围进行操作，而不是一次对每个单元格进行操作。（出于这个原因，您可能还想用 Application.ScreenUpdating = False 包装它
