Use Excel VBA to Color Rows by Yes and No Values
Question details
Use Excel VBA to color rows according to their Yes and No status 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.
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.
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
In Excel, open the workbook and choose Developer > Visual Basic.
In the VBA editor, choose Insert > Module and paste the macro above. Adjust the row range and status-column references if needed.
Return to Excel, select the worksheet with the status table, then choose Developer > Macros. Select ColorStatusRows and click Run.
Choose Home > Conditional Formatting > Manage Rules to review the new rules and resolve any conflicts with existing formatting.

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. 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. 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. Create the red rule: Add another formula-based rule with =COUNTIF($J3:$O3,"No")=COLUMNS($J3:$O3) and set a red fill.
- 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. 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.

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.




