1

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.

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)

Improve this question
1
  • Please post your current code Commented Nov 26, 2008 at 14:20

3 Answers 3

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

guys tried both and no joy :( i am defining rane 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
I have changed my code to use your setup for colRange. I just picked a value of 20 for rnum. I have tested this out in Excel 2000, and it works like a charm. What version of Excel do you have?
0

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?

Improve this answer

Comments

0

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

Improve this answer

Comments