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

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel