Formatting Issues

How to Change an Excel Cell Color Based on Text in Other Cells

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to dynamically change the background color of a specific target cell (e.g., to green or yellow) based on whether other related cells contain text or remain empty.

Product: Excel / Spreadsheet
Device & OS: not provided
Scenario: Setting up visual indicators in a worksheet where a status cell updates its color depending on data entry in surrounding cells.
Observed behavior: The target cell should automatically apply different background fill colors to reflect the presence or absence of text in a designated range.

Before you start

Identify the exact target cell you want to highlight and the specific range of related cells that will dictate the color change before creating your formatting rules.

Solution 1Recommended

Use Conditional Formatting with Formula Rules

Apply a formula-based conditional formatting rule using the COUNTBLANK function to determine if a range of cells is empty or contains text.

Using a formula within conditional formatting allows a single cell to evaluate the status of multiple other cells at once. The COUNTBLANK function is highly effective for checking if a specific range of cells contains data or is entirely empty, triggering different color changes based on the results.

Select the Target Cell

Click on the specific cell where you want the background color to change dynamically.

Open Conditional Formatting

Navigate to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Choose Formula Rule Type

In the New Formatting Rule dialog box, select the option that says 'Use a formula to determine which cells to format'.

Set Rule for Blank Cells (Green)

To turn the cell green when the related range (e.g., B3:F3) is empty, enter the formula =COUNTBLANK(B3:F3)=5 (assuming there are 5 cells in the range). Click 'Format', choose a green fill color, and click 'OK'.

Set Rule for Filled Cells (Yellow)

Repeat the process to create a second rule for the same target cell. Use the formula =COUNTBLANK(B3:F3)<5, set the format fill to yellow, and click 'OK'. This turns the cell yellow if any of the 5 cells contain text or values.

Adjust Cell References: Make sure to adjust the cell range references (B3:F3) and the expected count (5) in the formula to match the actual layout and size of the range in your specific worksheet.

Seamless Data Formatting

Automate Cell Colors Easily in WPS Spreadsheet

WPS Spreadsheet offers an intuitive interface for advanced conditional formatting, allowing you to quickly set up formula rules that change cell colors based on other cells, improving your data visualization.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook. Click on the target cell that requires color formatting.
2. Access Conditional Formatting: Go to the 'Home' tab, click 'Conditional Formatting', and choose 'New Rule'.
3. Apply the Formula: Select 'Use a formula to determine which cells to format', type your COUNTBLANK formula (e.g., =COUNTBLANK(B3:F3)=5), and configure the background color.
4. Save and Sync: Click 'OK' to apply. Because WPS is highly compatible, these rules will function perfectly even if you later open the file in Excel.

Free to use with comprehensive data analysis and spreadsheet capabilities.Fully compatible with Microsoft Excel (.xlsx) formats and conditional formatting rules.User-friendly conditional formatting manager to track multiple overlapping rules.Lightweight application that runs smoothly across Windows, Mac, and mobile devices.

microsoft office alternative - wps office

Frequently Asked Questions

Can I change a cell color based on a specific text word rather than just checking if it is blank?

Yes. You can use the COUNTIF function in your conditional formatting rule. For example, using the formula =COUNTIF(B3:F3, "Completed")>0 will change the target cell's color if the word 'Completed' appears anywhere in the specified range.

Why is my conditional formatting formula applying the wrong color when copied to other rows?

This usually occurs because of absolute cell references (the dollar signs, like $B$3). If you want to copy the rule down to other rows so it checks relative cells (like B4:F4, B5:F5), remove the dollar signs from the row numbers in your formula (e.g., use B3:F3 instead of $B$3:$F$3).

How do I remove conditional formatting rules if I made a mistake?

Select the cell with the formatting, go to the 'Home' tab, click 'Conditional Formatting', hover over 'Clear Rules', and choose 'Clear Rules from Selected Cells'.

Document & File Issues