Formatting Issues

How to Control Conditional Formatting Colors Based on Cell Values in Excel

Aamir Naveed Akram Sep 27, 2026 869 views

Question details

Set up dynamic conditional formatting that highlights rows based on dates and colors, but removes all formatting if the status changes to Closed or Cancelled.

How to Control Conditional Formatting Colors Based on Cell Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking project or task statuses where highlighting needs to automatically disappear for completed items and reactivate when the status becomes active again.
Observed behavior: The user needs to configure multiple overlapping rules using formulas and rule priority so that the Closed/Cancelled status overrides all other highlighting conditions.

Before you start

Ensure your dataset is organized in a clear tabular format and identify the exact column (e.g., Column C for Status) that will trigger the formatting suppression.

Solution 1Recommended

Use Formula-Based Conditional Formatting with 'Stop If True'

Create multiple conditional formatting rules and manage their priority to turn off colors based on specific text statuses before applying active formatting.

To achieve complex conditional formatting without VBA, you must utilize the 'Conditional Formatting Rules Manager'. By setting up a high-priority rule that looks for 'Closed' or 'Cancelled' and checking 'Stop If True', Excel will ignore any lower-priority color rules for those specific rows.

Select your data range

Highlight the entire dataset or range of cells where you want the conditional formatting to apply, starting from the first data row (e.g., A2:F100).

Create the suppression rule

Go to the Home tab, click on 'Conditional Formatting', and select 'New Rule'. Choose 'Use a formula to determine which cells to format'.

Enter the status formula

In the formula bar, type a formula that locks the status column but leaves the row relative, such as =OR($C2="Closed", $C2="Cancelled"). Do not set any format (leave it as No Format Set), then click OK.

Create the active highlighting rules

Add your subsequent rules for dates or active items (e.g., highlighting past dates in red). Click 'Conditional Formatting' > 'New Rule' again and set up your specific criteria.

Enable Stop If True

Navigate to Home > Conditional Formatting > Manage Rules. Ensure your suppression rule (Closed/Cancelled) is at the very top of the list. Check the 'Stop If True' box next to it and click Apply.

Use Formula-Based Conditional Formatting with 'Stop If True'

Absolute vs. Relative References: Using the dollar sign ($) before the column letter (e.g., $C2) ensures the entire row evaluates the status column correctly, applying the format across all columns in that row.

Work Smarter with WPS

Easily Manage Conditional Formatting with WPS Spreadsheet

WPS Office Spreadsheet provides an intuitive Conditional Formatting Rules Manager for applying complex, multi-layered rules and custom formulas, helping you automate data visualization effortlessly.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your .xlsx file, and highlight the data range you wish to format.
2. Access Conditional Formatting: Navigate to the Home tab on the ribbon, click 'Conditional Formatting', and select 'Manage Rules' from the dropdown menu.
3. Add a new formula rule: Click 'New Rule', select 'Use a formula to determine which cells to format', and input your logic (e.g., =$D2="Closed").
4. Set rule hierarchy and Stop If True: Back in the Rules Manager, use the up/down arrows to place your new rule at the top, check 'Stop If True', and click OK to apply.

Intuitive Conditional Formatting Rules Manager for easy hierarchy adjustment100% compatible with Microsoft Excel (.xlsx) files, formulas, and formatting rulesFree, lightweight, and fast alternative to Microsoft OfficeFamiliar user interface makes transitioning seamless

microsoft office alternative - wps office

Frequently Asked Questions

How do I highlight an entire row based on one cell's value?

When creating your conditional formatting formula, use an absolute column reference and a relative row reference. For example, use =$A2="TargetValue" instead of =A2="TargetValue". This tells Excel to check column A for every cell in row 2.

What does 'Stop If True' do in the Conditional Formatting Rules Manager?

'Stop If True' halts the evaluation of subsequent formatting rules if the current rule's condition is met. This is highly useful for overriding general formatting rules (like color-coding by date) when a specific condition (like 'Closed') is satisfied.

Why is my conditional formatting applying to the wrong rows?

This usually happens if your formula's row reference doesn't match the first row of your selected range. If your selected data range starts at row 3 (e.g., A3:F50), your formula must also reference row 3 (e.g., =$C3="Closed").

Will these formatting rules carry over if I open the file in WPS Office?

Yes, WPS Spreadsheet is fully compatible with Excel's conditional formatting rules, including custom formulas and rule hierarchies like 'Stop If True'.

Document & File Issues