基于细胞价值的彩色细胞

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)

3 个解决方案

#1

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

#2

Assume that value is the number stored in the column then:

假设该值是存储在列中的数字,则:

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应该是什么颜色?红色还是黄色?

#3

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:Insert,Name,Define):

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:

如果它是范围对象,则在代码中定义:

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进行包装

#1