Consulting

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
    Joined
    May 2015
    Posts
    87
    Location

    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
    Joined
    Jun 2017
    Posts
    6
    Location
    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

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    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
  •