Fix Excel Conditional Formatting Rules That Do Not Apply in WPS Spreadsheets
Question details
The user needs to troubleshoot conditional formatting rules in WPS Spreadsheets so that completed rows turn green and sold rows turn pink, including newly added entries.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook uses conditional formatting to color completed rows green and sold rows pink, but the formatting stops working for new entries or certain status values.
- Observed behavior
- Some rows receive incorrect formatting or no formatting, even though the rules exist and work for other rows.
Open the workbook in WPS Spreadsheets and select one affected cell that should be formatted but is not. Make sure the Home tab is visible so you can open Conditional Formatting > Manage Rules and inspect the applied rules.
Check the Applied Range and Rule Order in Manage Rules
Use this solution first to confirm that the rule is actually applied to the affected cells and that more specific rules are not overriding the expected green or pink formatting.
Conditional formatting rules are evaluated in order, and the first rule that returns true can block later rules when Stop If True is enabled. The Applies to range and the rule formula are the most common reasons cells receive incorrect conditional formatting.
Click a cell that is not showing the correct color, then go to Home > Conditional Formatting > Manage Rules.
In the Conditional Formatting Rules Manager, select each rule and check the Applies to box. Confirm it includes the row or range where new entries and status values are entered.
Click Edit Rule and verify that the formula or condition matches the correct status text, such as =$C2="Completed" for green and =$C2="Sold" for pink, with no extra spaces.
Use the Move Up or Move Down buttons so the green and pink rules are in the intended order. If a rule should not block later rules, clear its Stop If True checkbox.
Extend Conditional Formatting to New Rows with Format Painter
Use this solution when existing rules work for the original rows but new entries below or beside that range do not receive the expected formatting.
Fix Conditional Formatting in WPS Spreadsheets
Use WPS Spreadsheets' Manage Rules, range settings, and Format Painter to make completed rows turn green and sold rows turn pink consistently for new entries and status changes.
- 1. Open Manage Rules: In WPS Spreadsheets, select an affected cell and go to Home > Conditional Formatting > Manage Rules.
- 2. Edit the rule formula: Click Edit Rule and set the formula, for example =$C2="Completed" for green or =$C2="Sold" for pink, matching the actual status cell.
- 3. Expand the Applies to range: In the Applies to box, enter the full data range, such as =$A$2:$D$500, so new rows are included.
- 4. Reorder and apply: Move the green and pink rules into the intended order and click Apply to update the workbook.
Frequently Asked Questions
Why does Excel conditional formatting work for some rows but not new rows?
The rule's Applies to range likely does not include the new rows. Select an affected cell, open Home > Conditional Formatting > Manage Rules, select the rule, and expand the Applies to range. You can also use Format Painter to copy the formatting to new rows.
How do I stop one conditional formatting rule from overriding another?
Rules are evaluated in order. Select the affected cell, open Home > Conditional Formatting > Manage Rules, and use Move Up or Move Down to reorder the rules. If a rule is blocking later rules, clear its Stop If True checkbox.
Can WPS Spreadsheets open Excel conditional formatting rules?
Yes. WPS Spreadsheets supports formula-based conditional formatting and Excel .xlsx workbooks, so you can open, edit, and save workbooks without losing conditional formatting rules.
Why does my conditional formatting formula not match text that looks correct?
The cell may contain extra spaces or different capitalization. In Manage Rules, choose Edit Rule and use a formula such as =TRIM($C2)="Completed" to ignore leading and trailing spaces, or check the exact status value.
How do I apply conditional formatting to an entire row based on a status cell?
Select the full data range, open Home > Conditional Formatting > New Rule, choose formula-based formatting, and enter a formula like =$C2="Completed" or =$C2="Sold". Lock the column with $ so each row checks its own status value.
