Consulting

Results 1 to 3 of 3

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

  1. #1

    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
    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 
    
    
    Formatting tags added by mark007

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,228
    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 
    
    
    Formatting tags added by mark007
    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
  •