VBA For filling in records in excel with a color based on a number

I have a command button that I've coded on an access form that opens and creates an excel document. In Column C I have values in each cell.

I need to write something that colors everything to the right based on the value in that cell.

IF C3 > 7 Then color everything to right green, IF C3 is Between 0 and 7 then color everything to the right yellow, if C3 is <= 0 then color everything to the right Red.

I will need this to loop through the column until it hits an empty cell.

cleteh,

Here is some code that I modified that should do what your looking for, or at least point you in the right direction.

Code:

---

With .Range("C3:C" & i).FormatConditions.Add(xlCellValue, xlGreater, 7)
            .Interior.Color = RGB(157, 255, 157)
            .Font.Color = RGB(157, 255, 255)        'green
        End With
        With .Range("C3:C" & i).FormatConditions.Add(xlCellValue, xlBetween, 0, 7)
            .Interior.Color = RGB(255, 255, 0)
            .Font.Color = RGB(255, 255, 0)        'Yellow
        End With
        With .Range("C3:C" & i).FormatConditions.Add(xlCellValue, xlLessEqual, 0)
            .Interior.Color = RGB(255, 53, 53)
            .Font.Color = RGB(255, 53, 53)          'red
        End With

---

I don't like using RGB values in Excel Cells. Results can be unexpected. This code run on an empty sheet will list all the Excel Colors and their Indices.

Code:

---

Option Explicit

Sub ListColorIndices()
Dim i As Long

With ActiveSheet
  Range("A1") = " Color "
  Range("B1") = "ColorIndex"
 
  With Range("A1:B1")
    .Font.Bold = True
    .Columns.AutoFit
  End With
 
  For i = 1 To 56
    With Cells(i + 1, "A")
      .Interior.ColorIndex = i
      .Offset(, 1).Value = i
    End With
  Next
End With
End Sub

---

If Dr. Coz's Conditional Formatting doesn't work for you, let us know.