How to Set Persistent Conditional Formatting Colors in Excel
Question details
The user wants to set up conditional formatting where a cell turns green under specific conditions but turns red after a due date, and permanently stays red even if the source data is later modified or removed.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking shipping or delivery statuses where an overdue status (red color) must permanently lock into place once triggered, regardless of subsequent data edits.
- Observed behavior
- Standard conditional formatting dynamically recalculates whenever the source cells change, meaning the cell loses its red color if the underlying data that triggered the rule is cleared or updated.
Before proceeding, ensure you understand that this workaround requires enabling iterative calculation, which allows circular references in your workbook and may impact overall formula calculation performance.
Use a Helper Column with Iterative Calculation
Since standard conditional formatting recalculates constantly, use a self-referencing formula in a helper column to lock the TRUE/FALSE state permanently once triggered.
By default, conditional formatting is dynamic. To force a cell to remember a previous state (such as turning red and staying red), you must introduce a circular reference.
By enabling iterative calculation, you can write a formula that checks its own cell value. Once the condition evaluates to TRUE, it remains TRUE even if the original data changes.
Go to File > Options > Formulas. Under the Calculation options section, check the box for 'Enable iterative calculation' and click OK.
In an empty column (e.g., Column E), enter a formula that checks the criteria and references itself. For example: =OR(AND(A2=0,B2=0,C2<D2),E2=TRUE). This means if the primary criteria are met OR the cell E2 is already TRUE, the result will be TRUE.
Click the bottom-right corner of the cell containing your new formula (E2) and drag it down to apply it to all relevant rows in the dataset.
Select the cells you want to change color. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter the formula =E2=TRUE in the rule box, click Format to choose a red fill color, and click OK. The target cell will now stay red permanently once E2 becomes TRUE.

Manage Complex Conditional Formatting with WPS Spreadsheets
WPS Spreadsheets provides comprehensive support for iterative calculations, circular references, and advanced conditional formatting rules, empowering you to build robust tracking systems with permanent states.
- 1. Enable Iterative Calculation: Open your spreadsheet in WPS Office, go to Menu > Options > Calculation, and check 'Iterative calculation'.
- 2. Configure the Helper Column: Set up your self-referencing helper column by typing your specific criteria alongside the self-referencing check, such as =OR([Your Conditions], E2=TRUE).
- 3. Apply Conditional Formatting: Navigate to Home > Conditional Formatting > New Rule, select 'Use a formula to determine which cells to format', and link it directly to your helper column to secure persistent colors.

Frequently Asked Questions
Why does my conditional formatting change when I update the source data?
Standard conditional formatting is designed to be fully dynamic. Whenever a referenced cell changes, Excel instantly recalculates the condition and updates the applied format accordingly, discarding previous states.
How do I enable iterative calculation in Excel?
Click on File > Options > Formulas. In the Excel Options dialog, look under the Calculation options section, check the box for 'Enable iterative calculation', and then click OK to save your changes.
How can I clear the persistent red color if the data was entered by mistake?
Because the helper cell securely locks into a TRUE state via a circular reference, you must manually delete the formula in that specific helper cell. You can then drag the formula down again from an adjacent cell to reset the condition.
Does enabling iterative calculation slow down my workbook?
It can in some cases. Iterative calculation allows circular references to loop repeatedly up to a maximum number of iterations. If your workbook contains a large volume of complex formulas, this setting might noticeably decrease calculation performance.




