Formatting Issues

How to Change a Merged Cell's Color Based on a Range Condition in Excel

Steven Parker Oct 22, 2026 868 views

Question details

The user needs to automatically change the background color of a merged cell when all individual cells within a corresponding adjacent range contain the exact text 'Approved'.

How to Change a Merged Cell's Color When a Range is Approved

Product: Excel
Device & OS: not provided
Scenario: Applying a conditional formatting rule to a merged area that relies on evaluating multiple criteria across a separate array of cells.
Observed behavior: The merged cell successfully updates its fill color only when every cell in the defined range matches the required condition.

Before you start

Ensure that the cells in your target range do not contain leading or trailing spaces, as hidden spaces will prevent the text condition from being recognized correctly by the formula.

Solution 1Recommended

Use the COUNTIF Formula in Conditional Formatting

This method counts the exact number of matching cells in your range and triggers the formatting rule only when all required cells meet the criteria.

Using the COUNTIF function is the most reliable way to evaluate multiple cells for a specific text string within conditional formatting rules, avoiding the common pitfalls of array formulas.

Select the Merged Cell

Click to select the merged cell (for example, A5:A9) that you want to apply the background color to.

Open Conditional Formatting

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

Choose Formula Option

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

Enter the COUNTIF Formula

In the formula input box, enter =COUNTIF($AD$5:$AD$9,"Approved")=5 to check if all five cells in the range AD5:AD9 contain the word 'Approved'.

Apply Formatting

Click the 'Format' button, navigate to the 'Fill' tab, choose your desired background color, and click 'OK' twice to apply the rule.

Use the COUNTIF Formula in Conditional Formatting

Absolute References: Using dollar signs ($) in the formula creates absolute references, ensuring the range being checked won't shift unexpectedly if you copy the format to other cells.

Solution 2

Use the AND Function as an Alternative

An alternative formula method using the AND function to verify if every cell in the specified range matches the required text.

Efficient Spreadsheet Formatting

Use WPS Spreadsheet for Advanced Conditional Formatting

WPS Office provides an intuitive Conditional Formatting tool that seamlessly supports complex logical formulas like COUNTIF and AND. It's a highly capable, free spreadsheet solution.

1. Open WPS Spreadsheet: Launch WPS Office on your device and open your spreadsheet document.
2. Select Your Cells: Highlight the merged cell you want to format based on the status of your range.
3. Create Conditional Formatting Rule: Navigate to the Home tab, click Conditional Formatting > New Rule, choose the formula option, and input your logic.

Fully compatible with Microsoft Excel formulas and conditional formatting rules.Lightweight installation with blazing fast performance for large datasets.Clean, user-friendly interface makes creating custom rules straightforward.Free to use with comprehensive data analysis and visualization tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting formula not changing the cell color?

The most common reasons are spelling mismatches, hidden spaces in the text cells, or incorrect cell referencing. Ensure you are using absolute references (like $AD$5:$AD$9) so the formatting rule doesn't evaluate the wrong range.

Can I apply this formatting rule to multiple merged cells simultaneously?

Yes. You can select all the merged cells you wish to format at once. Just make sure your formula references use mixed referencing (locking the column but not the row) if each merged cell evaluates a different row range.

Does the COUNTIF formula work for numeric values as well?

Absolutely. You can replace the text condition with a numeric condition inside the COUNTIF formula, such as replacing "Approved" with ">10" to check if a range of numbers meets a specific threshold.

Document & File Issues