Formatting Issues

How to Color Excel Cells Red for CLOSED and Green for OPEN

Maira Mehtab Sep 27, 2026 870 views

Question details

The user wants to automatically apply specific fill colors to Excel cells based on their text values, specifically formatting cells containing the word CLOSED in red and OPEN in green.

Product: Excel
Device & OS: not provided
Scenario: Tracking project statuses, task lists, or categorizing data in a spreadsheet where visual cues are needed.
Observed behavior: Cells need to automatically change color based on the exact text entered into them.

Before you start

Highlight the specific column or range of cells where you plan to enter the statuses 'CLOSED' or 'OPEN' before applying the formatting rules.

Solution 1Recommended

Use Conditional Formatting for Specific Text

The most efficient way to change cell colors based on specific text is by using Excel's built-in Conditional Formatting feature.

Conditional formatting allows you to apply formatting—such as cell shading or font color—automatically based on the cell's value. You will need to create two separate rules on the same range of cells: one for the 'CLOSED' status and one for the 'OPEN' status.

Select the target cells

Click and drag to select the cells, column, or row where you will be typing the statuses.

Access Conditional Formatting

Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting', choose 'Highlight Cells Rules', and then select 'Text that Contains'.

Create the rule for CLOSED

In the dialog box, enter 'CLOSED' in the text field. From the adjacent drop-down menu, choose a red format (such as 'Light Red Fill with Dark Red Text') and click OK.

Create the rule for OPEN

Keep the same cells selected and go back to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. This time, type 'OPEN', select a green format from the drop-down menu (such as 'Green Fill with Dark Green Text'), and click OK.

Dynamic Updates: Once these rules are applied, any time you type OPEN or CLOSED into these cells, they will automatically update to the designated color.

Manage Data Effectively

Use WPS Spreadsheet to Apply Conditional Formatting

WPS Spreadsheet offers a highly intuitive Conditional Formatting tool that perfectly supports Microsoft Excel files. You can easily set up automated highlights for cell statuses like 'OPEN' and 'CLOSED' to make your data visually clear.

1. Open your file in WPS Spreadsheet: Launch WPS Spreadsheet and select the data range where the status text will appear.
2. Set the first rule: Go to the Home tab, click 'Conditional Formatting', select 'Highlight Cells Rules', and choose 'Text that Contains'. Type 'CLOSED' and apply a red fill.
3. Set the second rule: Repeat the process for the same range: select 'Text that Contains', type 'OPEN', and apply a green fill.

Fully compatible with Microsoft Excel (.xlsx and .xls) formats and formatting rules.Easy-to-use Conditional Formatting tools for text, dates, and numbers.Lightweight software with a familiar interface that requires zero learning curve.Completely free alternative to Microsoft Office for everyday data tracking and analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Can I format the entire row instead of just the single cell containing the text?

Yes. To format an entire row, you must select the whole data range, choose Conditional Formatting > New Rule > 'Use a formula to determine which cells to format', and enter a formula locking the column, such as =$A1="CLOSED" (assuming column A contains the status).

Why aren't my cells changing color when I type the text?

Ensure there are no extra leading or trailing spaces in your cells. A cell containing ' CLOSED ' might not trigger a strict match rule. Additionally, check Conditional Formatting > Manage Rules to ensure no conflicting rules are overriding your new color rules.

Can I add more colors for other statuses like PENDING?

Absolutely. You can add as many conditional formatting rules as you need to the same range of cells. Just repeat the 'Text that Contains' process and enter 'PENDING' with a yellow or orange format.

How do I remove the color rules if I no longer need them?

Select the formatted cells, go to the Home tab, click Conditional Formatting, select 'Clear Rules', and choose 'Clear Rules from Selected Cells'.

Document & File Issues