How to Use Excel Conditional Formatting in WPS Office for Rejected Rows with Blanks
Question details
The user needs conditional formatting rules that turn column H red when it contains Rejected, turn the whole row red when no cells in the checked range are blank, and turn the row yellow when one or more cells are blank.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing a worksheet in which rows marked Rejected must be visually flagged by completeness, with column H and row-level colors updating automatically.
- Observed behavior
- The desired result is a red fill in column H for Rejected entries, a red full-row fill for complete rows, and a yellow full-row fill for rows that contain blank cells.
Open your workbook in WPS Office Spreadsheet and identify the exact data range and number of columns you want to check. Keep the text in column H consistent, such as using Rejected with the same capitalization.
Create Three Conditional Formatting Rules for Rejected Rows and Blank Cells
Use this method when column H records a Rejected status and you want WPS Spreadsheet to color-code rows based on whether all required cells are filled.
The formulas rely on COUNTA to count non-blank cells. Adjust the column range and the number 14 to match your worksheet; otherwise the complete-row and blank-row colors will target the wrong rows.
Select H2:H100 or your actual column H range. In WPS Office Spreadsheet, click Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format, enter =$H2="Rejected", click Format, choose a red fill, and confirm.
Select A2:N100 or your full table range, open Home > Conditional Formatting > New Rule, and use =AND($H2="Rejected",COUNTA($A2:$N2)=14). Apply a red fill and adjust 14 to your column count.
With the same A2:N100 range selected, create another rule using =AND($H2="Rejected",COUNTA($A2:$N2)<14). Apply a yellow fill and confirm. Use Home > Conditional Formatting > Manage Rules to arrange the rules in the intended priority order.
Color-Code Rejected Rows with WPS Office Spreadsheet Conditional Formatting
WPS Office Spreadsheet can recreate this Excel conditional-formatting scenario with the same formula-based rules. Select your range, add the three rules, adjust the column count, and WPS will update red and yellow highlights automatically while preserving full .xlsx compatibility.
- 1. Open your workbook: Open your Excel .xlsx file in WPS Office Spreadsheet, and click the Home tab.
- 2. Select the range and open Conditional Formatting: Select A2:N100 or your actual data range, click Conditional Formatting, then choose New Rule.
- 3. Add the three conditional formatting rules: Use the formulas =$H2="Rejected", =AND($H2="Rejected",COUNTA($A2:$N2)=14), and =AND($H2="Rejected",COUNTA($A2:$N2)<14), applying red or yellow fills as needed.
- 4. Set rule priority: Open Conditional Formatting > Manage Rules, reorder the rules so the column H red rule and row rules are evaluated in the intended order, then click OK.
- 5. Verify the result: Type Rejected in column H and clear or fill cells to confirm that the rows change color automatically.
Frequently Asked Questions
How do I adjust the formulas if my table has more or fewer than 14 columns?
In WPS Office Spreadsheet, replace 14 in the COUNTA formulas with the number of columns in your selected data range. For example, if your range is A2:M2, use =COUNTA($A2:$M2)=13 and =COUNTA($A2:$M2)<13.
Why does the red row rule apply to a row that still has blanks?
Check the COUNTA value and the range in the formula. If the formula uses =14 but your actual data range has fewer than 14 columns, it will not behave as expected. Open Home > Conditional Formatting > Manage Rules, edit the rule, and correct the range and column count.
Does the formula distinguish between "Rejected" and "rejected"?
No. In WPS Office Spreadsheet and Excel, the comparison =$H2="Rejected" is case-insensitive, so rejected, Rejected, and REJECTED all trigger the rule.
Can I apply this formatting to an entire table that grows automatically?
You can select a larger range, such as A2:N1000, or convert your data to a formatted table and apply the rules to the table columns. The formulas will keep working for new rows as long as the references are relative and the selected range includes them.
