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

Question

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...

Answer 1

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