Results 1 to 3 of 3

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

  1. #1
    VBAX Regular
    May 2015

    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.

  2. #2
    VBAX Regular
    Jun 2017

    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

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Oct 2006
    Near Columbia
    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
      End With
      For i = 1 To 56
        With Cells(i + 1, "A")
          .Interior.ColorIndex = i
          .Offset(, 1).Value = i
        End With
    End With
    End Sub
    If Dr. Coz's Conditional Formatting doesn't work for you, let us know.
    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts