How to change background or font color based on cell value in Excel?
When you deal with huge data in Excel, you may want to pick out some value and highlight them with specific background or font color. This article is talking about how to change the background or font color based on cell values in Excel quickly.
Method 2: Change background or font color based on cell value statically with Find function
Method 3: Change background or font color based on cell value statically with Kutools for Excel
Method 1: Change background or font color based on cell value dynamically with Conditional Formatting
The Conditional Formatting feature can help you to highlight the values greater than x, less than y, or between x and y.
Supposing you have a range of data, and now you need to color the values between 80 and 100, please do with the following steps:
1. Select the range of cells that you want to highlight certain cells, and then click Home > Conditional Formatting > New Rule, see screenshot:
2. In the New Formatting Rule dialog box, select the Format only cells that contain item in the Select a Rule Type box, and in the Format Only Cells with section, specify the conditions that you need:
- In the first drop down box, select the Cell Value;
- In the second drop down box, select the criteria:between;
- In the third and fourth box, enter the filter conditions, such as 80, 100.
3. Then, click Format button, in the Format Cells dialog box, set the background or font color as this:
| Change the background color by cell value: | Change the font color by cell value |
| Click Fill tab, and then choose one background color you like | Click Font tab, and select the font color you need. |
 |
 |
4. After select the background or font color, click OK > OK to close the dialogs, and now, the specific cells with value between 80 and 100 are changed to the certain the background or font color in the selection. See screenshot:
| Highlight specific cells with background color: | Highlight specific cells with font color: |
 |
 |
Note: The Conditional Formatting is a dynamic feature, the cell color will be changed as the data changes.
Method 2: Change background or font color based on cell value statically with Find function
Sometimes, you need to apply a specific fill or font color based on cell value and make the fill or font color not change when the cell value changes. In this case, you can use the Find function to find all the specific cell values and then change the background or font color to your need.
For example, you want to change the background or font color if the cell value contains “Excel” text, please do as this:
1. Select the data range that you want to use, and then click Home > Find & Select > Find, see screenshot:
2. In the Find and Replace dialog box, under the Find tab, enter the value that you want to find into the Find what text box, see screenshot:
3. And then, click Find All button, in the find result box, click any one item, and then press Ctrl + A to select all found items, see screenshot:
4. At last, click Close button to close this dialog. Now, you can fill a background or font color for these selected values, see screenshot:
| Apply the background color for the selected cells: | Apply the font color for the selected cells: |
 |
 |
Method 3: Change background or font color based on cell value statically with Kutools for Excel
Kutools for Excel’s Super Find feature supports lots of conditions for finding values, text strings, dates, formulas, formatted cells and so on. After finding and selecting the matched cells, you can change the background or font color to your desired.
After installing Kutools for Excel, please do as this:
1. Select the data range that you want to find, and then click Kutools > Super Find, see screenshot:
2. In the Super Find pane, please do the following operations:
- (1.) First, click the Values option icon;
- (2.) Choose the find scope from the Within drop down, in this case, I will choose Selection;
- (3.) From the Type drop down list, select the criteria that you want to use;
- (4.) Then click Find button to list all corresponding results into the list box;
- (5.) At last, click Select button to select the cells.
3. And then, all cells matching the criteria have been selected at once, see screenshot:
4. And now, you can change the background color or font color for the selected cells as you need.
Tips: With the Super Find function, you can also deal with the following operations quickly and easily:
Busy work on weekend, Use Kutools for Excel,
gives you a relaxing and joyful weekend!
On the weekend, the kids are clamoring to go out to play, but there is too much work surrounds you to have time accompany the family. The sun, the beach and the sea so far away? Kutools for Excel helps you to solve 80% Excel puzzles, save 80% work time.
- Get a promotion and increase salary are not faraway;
- Contains 300 advanced features, solve 1500 application scenarios, some features even save 99% work time;
- Become an Excel expert in 3 minutes, and get recognition from your colleagues or friends;
- No longer need to search solutions from Google, say goodbye to painful formulas and VBA codes;
- All repeated operations can be completed with only several clicks, free your tired hands;
- Only $39 but worth than the other people's $4000 Excel tutorial;
- Be chosen by 110,000 elites and 300+ well-known companies;
- 30-day free trial, and full money back within 60-day without any reason;
- Change the way you work, and then change your life style!
