vba problem using case to colour range based on number in cell

Question

hi using macro i have consolidated info from several wbooks into on sheet in new book. in one column i have created a named range called ColRange i have numbers ranging from -350 to 500 in this column I can't seem to write the vba to color these cells red(0-500) yellow(-5-0)and green(-350--5) any help would be appreciated thanks

Answer 1

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?

Answer 2

Have a look at conditional formatting. You may not even need VBA to do this.

That being said, the VBA code would 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

Answer 3

guys tried both and no joy :( i am defining range by the following Set colRange = Range(Cells(2, 9), Cells(rnum,9)).Select this highlights the cells so is obviously doing some thing any help

Answer 4

Hi all, wonder if you can help me with the above? I am fairly new to VBA and trying to get my head around it! I've used the code above to fulfil the following criteria:

I have 4 rows -

• First Row: Row 9, Column Q-AB (Actual Numbers A)
• Second Row: Row 10, Column Q-AB (Target Numbers A)
• Third Row: Row 11, Column Q-AB (Actual Numbers B)
• Fourth Row: Row 12, Column Q-AB (Target Numbers B)

I want to be able to change the colour of the cell in the first/third row based on the number in relation to the second/fourth row.

I.E. if the number in the "Actuals A" for the first row exceeds that of the "Targets A" in the second row, it should be red. If it equals, it should be amber, if it is less than, it should be green.

I've got to the point where I have it apparently working for only one column (Q9-Q12) however it's highlighting the entire column (from Q1 to Q12) - I am not sure how to have it only do Q9-Q12, and how to loop it round to say "now start at row 9, column R" once it's got to the end, and then continue doing this until it reaches AB12.

I wasn't entirely sure what the function of rnum was (why not define the row?) and assumed I had to convert the column letters into numerical positions? Probably all wrong but here's where I got to after most of yesterday!

Option Explicit

Private Sub colorit()
    Dim colRange As Range
    Dim rowNum As Integer
    Dim rnum As Integer

    rnum = 1
    Set colRange = Range(Cells(9, 17), Cells(rnum, 28))

    For rowNum = 1 To colRange.Columns.Count
        If colRange.Cells(rowNum, 1).Value < colRange.Cells((rowNum + 1), 1) Then
            colRange.Cells(rowNum, 1).Interior.Color = RGB(0, 255, 0)
        ElseIf colRange.Cells(rowNum, 1).Value = colRange.Cells((rowNum + 1), 1) Then
            colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 255, 0)
        ElseIf colRange.Cells(rowNum, 1).Value < colRange.Cells((rowNum + 1), 1) Then
            colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 0, 0)
        End If
        Next rowNum
End Sub

I am sure I am missing something daft or have just made a silly error somewhere but I can't for the life of me figure out what I need to do!

The reason I chose not to use conditional formatting is that I am trying to drag myself into learning VBA, and I want this to be scalable to situations where I may have more than three conditions.

Answer 5

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):

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