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

[cleteh]

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.

[drcoz]

cleteh,

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


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

[SamT]

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.

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.