excelvbaif-statementrange

How to apply a color format condition to a range of cells and highlight the line that matches the condition?


I have code (I got in a forum) for a CommandButton macro, that selects a wide range of cells in a worksheet, and highlights the selected cells background in different colors, depending on each of the three button states.

I adjusted the code a bit:

Private Sub CommandButton1_Click()
Dim Nam As Variant
Static c
Nam = Array("A", "B", "C", vbYellow, vbCyan, vbWhite)
If c = 3 Or c = "" Then c = 0
CommandButton1.Caption = Nam(c)
CommandButton1.BackColor = Nam(c + 3)

Range("A:G").Interior.ColorIndex = xlNone
Select Case Nam(c)
    Case "A": Range("A1:U1000").Interior.ColorIndex = 6
    Case "B": Range("A1:U1000").Interior.ColorIndex = 8
    Case "C": Range("A1:U1000").Interior.ColorIndex = 0
End Select
c = c + 1
End Sub

But I need that for each button state, it highlights (in each color of the three button states) only the rows of the selection block that contain a value inside an specific cell, which is always in the same column, inside each row.

Something like this:

If button state is A, then all the rows that contain an "y" inside the cell Jn (J1, J2, J3...) get highlighted in the color set by A. Something like If (Jn = "y") then highlight the whole line... n = n+ 1.... Goes to next line... repeat...

If button state is B, then all the rows that contain an "n" inside the cell Jn get highlighted in the color set by B

If button state is C, then all the rows that contain "y" or "n" get back to no fill in their cells...


Solution

  • In your Select statement, assign the ColorIndex to a variable. Then loop through each row in your range and check if each column J meets your criteria. If it does, apply the ColorIndex to that row.

    Private Sub CommandButton1_Click()
    Dim Nam As Variant
    Static c
    Nam = Array("A", "B", "C", vbYellow, vbCyan, vbWhite)
    If c = 3 Or c = "" Then c = 0
    CommandButton1.Caption = Nam(c)
    CommandButton1.BackColor = Nam(c + 3)
    
    Range("A:G").Interior.ColorIndex = xlNone
    
    Dim NewColor As Long
    Select Case Nam(c)
        Case "A": NewColor = 6
        Case "B": NewColor = 8
        Case "C": NewColor = 0
    End Select
    
    Dim rg As Range
    Dim i As Long
    Set rg = Range("A1:U1000")
    For i = 1 To rg.Rows.Count
        If Cells(i, "J") = "y" Then Range(Cells(i, "A"), Cells(i, "U")).Interior.ColorIndex = NewColor
    Next i
    c = c + 1
    End Sub