VBA & Macro Problems

Use Excel VBA to Color Rows by Yes and No Values

Rana GarciaRana Garcia Nov 18, 2026 868 views

Question details

Use Excel VBA to color rows according to their Yes and No status values.

Use Excel VBA to Color Rows by Yes and No Values
Product
Microsoft Excel
Device & OS
not provided
Scenario
A table has status values in columns J through O, and each row should be colored based on those values.
Observed behavior
Rows should be green when all statuses are Yes, red when all are No, and yellow when both Yes and No appear.
Before you start

Check that the status values are in J3:O22, or adjust the ranges in the macro for your table. The macro creates conditional formatting on the active worksheet; existing rules may affect the displayed colors.

Solution 1Recommended

Create row-coloring rules with an Excel VBA macro

Use this macro to add conditional formatting that updates when status values change.

The macro applies formatting to entire rows 3 through 22 and checks columns J through O. Rows containing both Yes and No are yellow, even if other cells are blank; rows with only one of those values and blanks are not colored by these rules.

Sub ColorStatusRows() Dim target As Range Set target = ActiveSheet.Range("3:22") With target.FormatConditions.Add(Type:=xlExpression, Formula1:="=COUNTIF($J3:$O3,""Yes"")=COLUMNS($J3:$O3)") .Interior.Color = RGB(0, 255, 0) End With With target.FormatConditions.Add(Type:=xlExpression, Formula1:="=COUNTIF($J3:$O3,""No"")=COLUMNS($J3:$O3)") .Interior.Color = RGB(255, 0, 0) End With With target.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(COUNTIF($J3:$O3,""Yes"")>0,COUNTIF($J3:$O3,""No"")>0)") .Interior.Color = RGB(255, 255, 0) End With End Sub

1
Open the VBA editor

In Excel, open the workbook and choose Developer > Visual Basic.

2
Add the macro

In the VBA editor, choose Insert > Module and paste the macro above. Adjust the row range and status-column references if needed.

3
Run it on the worksheet

Return to Excel, select the worksheet with the status table, then choose Developer > Macros. Select ColorStatusRows and click Run.

4
Check the rules

Choose Home > Conditional Formatting > Manage Rules to review the new rules and resolve any conflicts with existing formatting.

Create row-coloring rules with an Excel VBA macro
Avoid duplicate rules: Running the macro again adds another set of rules. Edit or remove the existing rules in Manage Rules before rerunning it. Formatting entire rows can also slow large workbooks; change the target to a table range such as A3:O22 if preferred.
WPS Spreadsheets

Color status rows without VBA in WPS Spreadsheets

Use formula-based conditional formatting in WPS Spreadsheets to apply the same green, red, and yellow status colors without running a macro.

  1. 1. Open and select the table: Open the workbook in WPS Spreadsheets and select the rows or table range to format, such as A3:O22.
  2. 2. Create the green rule: Choose Home > Conditional Formatting > New Rule and select the formula-based rule option. Enter =COUNTIF($J3:$O3,"Yes")=COLUMNS($J3:$O3) and set a green fill.
  3. 3. Create the red rule: Add another formula-based rule with =COUNTIF($J3:$O3,"No")=COLUMNS($J3:$O3) and set a red fill.
  4. 4. Create the yellow rule: Add a third formula-based rule with =AND(COUNTIF($J3:$O3,"Yes")>0,COUNTIF($J3:$O3,"No")>0) and set a yellow fill.
  5. 5. Apply and test: Apply the rules, then change a status cell in columns J through O to verify that its row color updates. Adjust the formulas if the first selected row or status columns differ.
Automatically updates row colors when Yes and No values changeSupports Microsoft Excel .xlsx workbooksUses familiar spreadsheet formatting controlsProvides a free way to edit and format spreadsheets
microsoft office alternative - wps office

Frequently Asked Questions

Will the colors update when I change a status?

Yes. The VBA macro creates conditional formatting rules, so Excel reevaluates the colors when the checked values change.

What happens if a row contains blank status cells?

A row is yellow if it contains both Yes and No, even with blanks. A row containing only Yes or only No alongside blanks does not meet any of the three rules.

How do I change the rows or columns checked by the macro?

In the VBA editor, change ActiveSheet.Range("3:22") to the rows or table area to format. Update each $J3:$O3 reference to match the status columns and first row of the new range.

Why is an existing color still showing?

Other conditional formatting rules may take precedence. Open Home > Conditional Formatting > Manage Rules to inspect their order and applied ranges.

How do I keep the macro in my Excel workbook?

Choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). The conditional formatting rules themselves do not require the macro to run again when values change.