Excel Conditional Formatting Based on Color: How It Works

How-to basics · Updated November 19, 2026

In Excel, conditional formatting can apply a color when a cell’s value meets a rule, but it cannot natively use a cell’s existing fill color as the rule itself. To make a color-coded result update reliably, store the underlying status or category in a cell and create conditional formatting from that value.

What “based on color” can mean in Excel

The phrase can describe two different tasks. You might want Excel to color cells automatically when their contents meet a condition, such as highlighting overdue dates in red. Or you might want a rule to inspect a fill color already applied manually and then format other cells accordingly. The first task is a normal conditional-formatting use; the second is not directly supported by Excel’s built-in conditional-formatting rules.

Conditional formatting is designed to evaluate cell contents, formulas, or certain built-in conditions and then apply a visual format. The fill color is usually the result of the rule, not data that the rule can read. This distinction matters because two cells that look identical can have different underlying values, and two cells with the same value can have different manually applied colors.

  • Want to color cells based on their values? Create a rule using a value condition or formula.
  • Want to use color as a category? Record that category in a cell, such as “Urgent” or “Review,” and base the rule on the text.
  • Want to find cells that already have a particular fill? Use Excel’s filter-by-color feature to inspect or filter the data; that is different from conditional formatting.

How conditional formatting works

A conditional-formatting rule checks a condition and applies a chosen format when that condition is true. Excel can check values directly, such as whether a number is below zero, or evaluate a formula for each cell in the rule’s “Applies to” range. The format might change the fill, font, border, or number format. It does not change the cell’s value.

For example, a color scale can shade low numbers one color and high numbers another. A rule can highlight dates before today, duplicate entries, or text that matches a label. In each case, Excel derives the visual result from cell contents or a formula, rather than detecting a color someone previously chose from the ribbon.

Rules can overlap. If more than one rule applies to a cell, the order shown in the Conditional Formatting Rules Manager can affect the result. Some rules can also use “Stop If True” to prevent lower-priority rules from being applied. When a result looks unexpected, check both the rule conditions and the order in which Excel evaluates them.

Apply a color when a value meets a condition

In Excel for Microsoft 365 on Windows, select the cells to format, then go to Home > Conditional Formatting. Choose a built-in option such as Highlight Cells Rules, Top/Bottom Rules, or Color Scales. Set the condition, choose a format, and confirm it. For a custom condition, choose New Rule and select a rule type that fits the task.

Suppose column B contains invoice balances and you want negative amounts to stand out. With balances in B2:B50, select that range and use Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter 0, choose a red fill, and confirm. Excel will apply the fill to negative values and update it if a balance changes.

For a more tailored rule, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Select the format with the Format button, then confirm. If the rule should color a whole row according to one column, select the entire target range first and use a formula that locks the test column while leaving the row relative.

With balances in B2:B50 and the target range A2:D50, use =$B2<0 as the formula. The dollar sign fixes the column reference at B, while the row number changes as Excel evaluates each row. Choose a red fill, and rows with a negative balance in column B will be highlighted.

The Conditional Formatting Rules Manager lets you inspect, edit, delete, and reorder rules. Open it from Home > Conditional Formatting > Manage Rules. Set “Show formatting rules for” to the current selection or the relevant worksheet when locating a rule that does not appear to work.

Use a helper value when color represents a category

If people are manually coloring cells to mean something—such as “approved,” “waiting,” or “needs attention”—put that meaning in a dedicated status column. A text label or code is easier to search, audit, sort, and use in formulas than a fill color. It also makes the workbook’s logic visible to someone who did not create it.

For example, put a status in column C and apply formatting to the corresponding record. With statuses in C2:C100 and the target range A2:D100, create a formula-based rule using =$C2="Urgent". Choose a red fill. If the status changes from “Urgent” to “Complete,” the rule no longer matches, so the conditional formatting updates automatically.

A helper column can also hold a short code such as R, A, or G, provided the workbook explains what each code means. Avoid relying on fill color alone when the color carries important information: colors may be hard to distinguish for some readers, may print differently, and can be lost when data is copied into another system. A readable label provides a non-color cue as well.

Can a formula test a cell’s existing fill color?

Ordinary worksheet formulas and the formula-based conditional-formatting rule do not provide a standard function that returns a cell’s fill color. A formula such as IF can test a value, but it cannot directly ask whether another cell was manually filled red. Changing a cell’s fill also does not count as a change to its value, so a value-based rule has no color change to evaluate.

Some workbooks use VBA or older macro-sheet techniques to inspect formatting, but these approaches have limitations. A VBA function that reads a fill may not recalculate just because someone changes the fill, and macros may be disabled by workbook security settings or unavailable in Excel for the web. Older GET.CELL-based workarounds require defined names and may also need recalculation. They are not a dependable substitute for storing the category as data.

If you inherit a workbook where color is the only record of a category, you can first filter or review the cells by color, then assign a corresponding status value in a helper column. After that, build conditional-formatting rules from the status. For a large or frequently changing workbook, consider a controlled data-entry process that records the status directly instead of asking people to communicate it only through manual fills.

Filter by color versus conditional formatting

Excel’s filter-by-color command is useful when the goal is to display or inspect cells with a particular existing fill or font color. In a filtered table or range, use the column’s filter arrow and choose Filter by Color, then select the available color. This narrows the displayed records; it does not create a live conditional-formatting rule or recolor other cells.

Conditional formatting is the better tool when the color should respond to changing data. For instance, a filter can show the rows that are currently yellow, while a rule based on a status value can make rows yellow whenever their status is “Waiting.” The latter remains tied to the meaning of the data even if someone changes the workbook’s color theme or formatting.

There is also a difference between manually applying a fill and using a conditional-formatting fill. A manually applied fill is ordinary formatting. A conditional fill appears because a rule evaluates as true. If you remove or change the rule, the conditional format changes; if you edit the source value, Excel reevaluates the condition.

Common problems and how to fix them

  • The wrong cells change color: Open Manage Rules and verify the “Applies to” range. A rule intended for rows 2 through 100 should not accidentally start on row 1 or stop before the last record.
  • The formula works on one row but not the others: Check whether the formula’s relative and absolute references match the selected range. For a whole-row rule based on column C, lock the column but usually leave the row relative, as in =$C2="Urgent".
  • The rule does not react to a fill change: That is expected when the rule is trying to use the existing fill as its condition. Use a value or helper status instead.
  • A manual fill seems to override the rule: Check for other conditional-formatting rules and their priority. Also distinguish a rule’s fill from ordinary formatting; clearing a cell’s manual format does not remove the rule that may format it again.
  • The color appears inconsistent after copying: Pasting can bring formatting or rules along with values. Review the destination range in Manage Rules and remove duplicate or unwanted rules.

When a rule is formula-based, remember that Excel evaluates it relative to the top-left cell of its “Applies to” range. If the range starts at A2, write the formula as though it is being checked for row 2. This simple alignment prevents many off-by-one errors.

Excel for Mac and Excel for the web

The core approach is the same in Excel for Microsoft 365 on Mac: select the range, open Home > Conditional Formatting, choose a rule or create a formula rule, and set the desired format. Ribbon layout and dialog appearance can vary slightly by platform or update, but the principle remains that the rule evaluates contents or formulas, not an existing fill color.

Excel for the web also supports common conditional-formatting rules and formula-based formatting. Some advanced rule-management options may be more limited or presented differently than in the desktop app. If a workbook uses macros to inspect color, the web version cannot run VBA; a helper column and standard conditional-formatting rules are the more portable choice.

Frequently Asked Questions

Can Excel conditional formatting use the color of another cell?
Not directly with a standard conditional-formatting formula. Put the meaning of that color in a value or status cell, then make the rule check that value.

Can I highlight cells that are already red?
Conditional formatting cannot natively detect a manually applied red fill as its condition. You can filter by the existing color to locate those cells, or convert the color’s meaning into a helper value and format from that.

Can conditional formatting change a cell’s color automatically?
Yes. Create a rule based on a value, formula, date, text, or other supported condition, then choose a fill or font format. The color updates when the condition’s underlying data changes.

Does changing a fill color recalculate a formula?
Usually not. A fill change is a formatting change, not a change to the cell’s value, and ordinary worksheet formulas do not recalculate based on fill color.

What is the best alternative to color-coded statuses?
Use a status column with clear labels such as “Urgent,” “Waiting,” and “Complete,” then apply conditional formatting to those labels. This keeps the meaning searchable and understandable without relying on color alone.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.