How to Change Cell Color Based on an IF Condition in Excel
Question details
The user wants to change a cell's color based on an IF condition or value range 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.
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.
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.
In Excel, select A2:A100, or select your own range and adjust the formula references to its first cell.
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.
Add another formula-based rule for the same range using =AND(ISNUMBER(A2),A2>=60,A2<=75). Choose a yellow fill and confirm.
Add a third formula-based rule using =AND(ISNUMBER(A2),A2>75). Choose a green fill and confirm.
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.

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. Open and select: Open your workbook in WPS Spreadsheets and select A2:A100, or the range you want to format.
- 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. 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. 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.

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.




