Skip to main content

Return to Answer

changed my code to reflect updates from OP
Source Link
James Eichele
James Eichele
  • 119.5k
  • 42
  • 183
  • 214

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:

dimPublic Sub colorit()  Dim colRange asAs Range dim  Dim rowNum asAs Integer Dim rnum As Integer   set rnum = 20 Set colRange = Range("ColRange"Cells(2, 9), Cells(rnum, 9))   for  For rowNum = 1 toTo colRange.Rows.Count   If colRange.Cells(rowNum, 1).Value <= -5 Then   colRange.Cells(rowNum, 1).Interior.Color = RGB(0, 255, 0) Else If  ElseIf colRange.Cells(rowNum, 1).Value <= 0 Then   colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 255, 0) Else If ElseIf colRange.Cells(rowNum, 1).Value <= 500 Then   colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 0, 0)   End If next  Next rowNum End Sub

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:

dim colRange as Range dim rowNum as Integer set colRange = Range("ColRange") 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) Else If colRange.Cells(rowNum, 1).Value <= 0 Then colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 255, 0) Else If colRange.Cells(rowNum, 1).Value <= 500 Then colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 0, 0) End If next rowNum

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
Source Link
James Eichele
James Eichele
  • 119.5k
  • 42
  • 183
  • 214

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:

dim colRange as Range dim rowNum as Integer set colRange = Range("ColRange") 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) Else If colRange.Cells(rowNum, 1).Value <= 0 Then colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 255, 0) Else If colRange.Cells(rowNum, 1).Value <= 500 Then colRange.Cells(rowNum, 1).Interior.Color = RGB(255, 0, 0) End If next rowNum