Formatting Issues

How to Change Cell Color Based on an IF Condition in Excel

Muhammad TalhaMuhammad Talha Nov 18, 2026 871 views

Question details

The user wants to change a cell's color based on an IF condition or value range in Excel.

How to Change Cell Color Based on an IF Condition in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Apply different colors to cells when their values fall within specified ranges.
Observed behavior
An IF formula can return a value but cannot directly change a cell's fill or font color.
Before you start

Identify the cells containing the values you want to color and decide where each color range starts and ends. The example below uses numeric values in A2:A100, with 60 and 75 included in the yellow range.

Solution 1Recommended

Use Excel Conditional Formatting to Color Cells by Value

Create formula-based rules that automatically apply colors as cell values change.

An IF formula returns a result; it does not set formatting. Conditional formatting evaluates rules separately and applies the chosen fill or font color.

1
Select the cells

In Excel, select A2:A100, or select your own range and adjust the formula references to its first cell.

2
Create the red rule

Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =AND(ISNUMBER(A2),A2<60). Select Format, choose a red fill, and confirm.

3
Create the yellow rule

Add another formula-based rule for the same range using =AND(ISNUMBER(A2),A2>=60,A2<=75). Choose a yellow fill and confirm.

4
Create the green rule

Add a third formula-based rule using =AND(ISNUMBER(A2),A2>75). Choose a green fill and confirm.

5
Check the rules

Open Home > Conditional Formatting > Manage Rules and verify that each rule applies to A2:A100. Change a value in the range to check that its color updates.

Use Excel Conditional Formatting to Color Cells by Value
Use font color instead: In each rule's Format dialog, choose a font color instead of a fill if you want to color the text. The ISNUMBER check prevents blank and text cells from being colored by these rules.
Conditional formatting in WPS Office

Color Cells by Value in WPS Spreadsheets

Use conditional formatting in WPS Spreadsheets to apply different cell colors based on value ranges, without writing a macro.

  1. 1. Open and select: Open your workbook in WPS Spreadsheets and select A2:A100, or the range you want to format.
  2. 2. Add the first rule: Go to Home > Conditional Formatting > New Rule, choose a formula-based rule, and enter =AND(ISNUMBER(A2),A2<60). Set the fill to red.
  3. 3. Add the remaining rules: Create a yellow rule using =AND(ISNUMBER(A2),A2>=60,A2<=75), then a green rule using =AND(ISNUMBER(A2),A2>75). Apply both to the same range.
  4. 4. Verify and save: Change a value in the selected range to check its color, then use File > Save or Save As to save the workbook.
Apply red, yellow, and green fills with formula-based rulesOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet ribbon to manage formattingWork with spreadsheets using the free WPS Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can an IF formula directly change a cell's fill color?

No. An IF formula returns a value, but it cannot set formatting. Use Home > Conditional Formatting to apply a color based on a condition.

Can I color a cell based on a value in another cell?

Yes. Select the cells to color, then create a formula-based rule under Home > Conditional Formatting > New Rule. For example, =B2<60 can format A2 based on B2; adjust the references for your selected range.

How do I change the font color instead of the background?

When creating or editing a conditional formatting rule, select Format and choose a color on the Font tab instead of the Fill tab.

What can I use if conditional formatting is unavailable?

In a macro-enabled desktop version of Excel, a Worksheet_Change VBA macro can set Interior.Color or Font.Color when a value changes. Macros must be permitted, and a Worksheet_Change macro does not automatically run when a formula result changes through recalculation.