Clever Geek Handbook

Colored cell based on the value in the cell

Using a macro, I have collected information from several books into one sheet in a new book.

In one column, I created a named range ColRange. This column is numbered from -350 to 500.

How to change the color of cells based on the text value in a cell.
red (0-500) yellow (-5-0) green (-350-5)

+1


source to share


3 answers


Take a look at conditional formatting . You might not even need VBA.

The VBA code will look something like this:



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
+3


source


Let's assume the value is a number stored in a column:

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 values ​​greater than 500 or less than -350 are either not possible, or your script confirmed it. Also, your ranges overlap a little, what color should be 0? Red or Yellow?

0


source


This is in response to the original question, this is a simple modification of Vincent's answer:

If it's a named range (using 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's a range object defined in 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 answer won't work because it tries to work with the entire ColRange range inside If Then, rather than work every cell at a time. (For this reason, you can also wrap it with Application.ScreenUpdating = False

0


source






More articles:


All Articles