Skip to main content

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 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:

doc highlight by value 1

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.

doc highlight by value 2

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.
doc highlight by value 3 doc highlight by value 4

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:
doc highlight by value 5 doc highlight by value 6

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:

doc highlight by value 7

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:

doc highlight by value 8

Tips: If you need to find the values with case sensitive or match the entire cell content, please click the Options button to get the advanced search options, such as "Match Case" and "Match entire cell content" as you need.

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:

doc highlight by value 9

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:
doc highlight by value 10 doc highlight by value 11

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:

doc highlight by value 12

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.

doc highlight by value 13

3. And then, all cells matching the criteria have been selected at once, see screenshot:

doc highlight by value 14

4. And now, you can change the background color or font color for the selected cells as you need.


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 Excel puzzles, save work time.

pic beach
  •  Get a promotion and increase salary are not faraway;
  •  Contains advanced features, solve 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!
 

Comments (79)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Good Day,

I have a schedule with different course modules that can be placed in different order ( 1 is metal, 2 is machining, 3 is refinishing, etc.) depending on course director preferences and staff availability.

What I am trying to do to wrap up the spreadsheet is color code the modules across the months based on their order in the overall course. So if Metal is first and is 35 days long, the first 35 days are background colored blue, but if machining is first and is 20 days long, then the first 20 days are purple.

How can you do and if statement that refers to a separate cell for the initial number of days, AND color the cells based on the range between 1 and 20 or 35 or whatever?

Can I do a conditional formatting If statement with a range between numbers?

This would have to be applied to all the modules (therefor multiple conditional formatting formulas) to account for any module being in any place in the order.
That is the issue I have. Please offer any suggestions. Note that I have built the spreadsheet without VBA. (I've never had the opportunity to learn it)
This comment was minimized by the moderator on the site
Can I give colour for a row of values with respect to previous value for a whole row.
Eg

23

45

32

56

can I give red for 23 and green for 45 and again red for 32 as it is less than the previous value and green for 56 as it is more than 32.
This comment was minimized by the moderator on the site
I do get to change a cell colour according to the text or letter in fill in the cell e.g.-H=YELLOW,E=GREEN
This comment was minimized by the moderator on the site
Hello, VANESSA,
For solving your problem, please apply the Conditional Formatting feature, in the New Formatting Rule dialog box, choose the conditions as following screenshot shown:
This comment was minimized by the moderator on the site
how do i change the colour of cell depending on what is in that cell IE. Y=green N=red.

thats how i need it if it has Y riten in the cell it turns green?
This comment was minimized by the moderator on the site
Hello, Leon,
To highlight the cells based on the cell value, you should apply the Conditional Formatting feature, go to the New Formatting Rule dialog box, and then do as the following screenshot shown to highlight the cells which are Y located as green:

Do with the same way to format the cells N as red color.

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
Trying to get a cell to highlight if its above a certain number, the problem is that the cell includes the number and a date. Is there a way i can get it to just look at the number? The cell will include a number+date.....1400(7/2/2018). I need the formulate just to look at the 1400 and not the date.
any ideas?
thanks
Z
This comment was minimized by the moderator on the site
Sir, I have value in cell A10, in A12. Now I entered a simple formula in A15 subtracting these (A10-A12). I want background color of result cell (A15) in green if A10 is large (means result in positive number) and background color in red if A12 is large ( result in negative number). Please provide solution
This comment was minimized by the moderator on the site
Hello,
You can select the formula cells, and then apply the Conditional Formatting > Greater than (0)-format it green, and then Less Than (0)-format it red, see screenshot:

Please try it, hope it can help you, thank you!
This comment was minimized by the moderator on the site
If I have a date in a cell, how do I make it change to orange when it is within 30 days of coming due, yellow when it is within 15 days of coming due and red when it exceeds the date (past due)
This comment was minimized by the moderator on the site
I thought this color of cells by value was going to be a real pain. It could not have been explained more easily and clearly. Thanks. It took no less than 1-2 minutes to understand what to do.
This comment was minimized by the moderator on the site
HI, I want if i change any value or number in any cell then that cell text color should get change for e.g. if number in any cell is 100 and I have to change it as 98 then that cell text 98 should change into red color. Is it possible in Excel?? Do clarify please
This comment was minimized by the moderator on the site
So I am creating a excel spreadsheet for fire department reports. What I am looking for is it to calculate the days a report is overdue, unless it has been completed: Column A Column B Column C Column D Column E Column F Incident # Incident Date Todays Date Completed Date Days Overdue Todays Date I want column B subtracting Column C to calculate in column E unless Column D is complete. I hope this makes sense. I want to use the conditional formatting to show me in colors how long a report is overdue (with the 3 color scale). I have the formula for the number of days it is overdue to have a correct value and color scale, but I want it to remain green when the report has been completed.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations