跳到主要内容

如何根据Excel中的单元格值更改背景或字体颜色?

当您在Excel中处理大量数据时,您可能希望选择一些值并用特定的背景或字体颜色突出显示它们。 本文讨论的是如何根据Excel中的单元格值快速更改背景或字体颜色。


方法1:使用条件格式根据单元格值动态更改背景或字体颜色

 

条件格式 功能可以帮助您突出显示大于x,小于y或x与y之间的值。

假设您有一系列数据,现在需要为80到100之间的值上色,请执行以下步骤:

1。 选择要突出显示某些单元格的单元格区域,然后单击 首页 > 条件格式 > 新规则,请参见屏幕截图:

doc按值突出显示1

2。 在 新格式规则 对话框中,选择 仅格式化包含以下内容的单元格 中的项目 选择规则类型 框,然后在 仅使用格式化单元格 部分中,指定所需的条件:

  • 在第一个下拉框中,选择 单元格值;
  • 在第二个下拉框中,选择条件:之间;
  • 在第三个和第四个框中,输入过滤条件,例如80、100。

doc按值突出显示2

3。 然后,点击 格式 按钮,在 单元格格式 对话框中,将背景或字体颜色设置为:

通过单元格值更改背景颜色: 通过单元格值更改字体颜色
点击 标签,然后选择您喜欢的一种背景色 点击 字体 标签,然后选择所需的字体颜色。
doc按值突出显示3 doc按值突出显示4

4. 选择背景或字体颜色后,单击 OK > OK 关闭对话框,现在,值在80到100之间的特定单元格将更改为所选内容中的特定背景或字体颜色。 看截图:

用背景色突出显示特定的单元格: 用字体颜色突出显示特定的单元格:
doc按值突出显示5 doc按值突出显示6

备注:本 条件格式 如果是动态功能,则单元格颜色将随着数据的变化而变化。


方法2:使用查找功能基于单元格值静态更改背景或字体颜色

 

有时,您需要根据单元格值应用特定的填充或字体颜色,并在单元格值更改时使填充或字体颜色不变。 在这种情况下,您可以使用 找到最适合您的地方 函数查找所有特定的单元格值,然后根据需要更改背景或字体颜色。

例如,如果单元格值包含“ Excel”文本,则要更改背景或字体颜色,请执行以下操作:

1。 选择您要使用的数据范围,然后单击 首页 > 查找和选择 > 找到最适合您的地方,请参见屏幕截图:

doc按值突出显示7

2。 在 查找和替换 对话框中的 找到最适合您的地方 标签,将您要查找的值输入到 查找内容 文本框,请参见屏幕截图:

doc按值突出显示8

Tips: 如果您需要查找区分大小写的值或匹配整个单元格内容的值,请单击 附加选项 按钮以获取高级搜索选项,例如“区分大小写“和”匹配整个单元格内容”。

3。 然后,单击 找到所有 按钮,在“查找结果”框中,单击任何一项,然后按 按Ctrl + A 要选择所有找到的项目,请参见屏幕截图:

doc按值突出显示9

4。 最后点击 关闭 按钮关闭此对话框。 现在,您可以为这些选定的值填充背景或字体颜色,请参见屏幕截图:

为所选单元格应用背景色: 应用所选单元格的字体颜色:
doc按值突出显示10 doc按值突出显示11

方法 3: 使用 Kutools for Excel 根据单元格值静态更改背景或字体颜色

 

Kutools for Excel超级查找 该功能支持用于查找值,文本字符串,日期,公式,格式化的单元格等的许多条件。 找到并选择匹配的单元格后,您可以将背景或字体颜色更改为所需的颜色。

安装后 Kutools for Excel,请这样做:

1。 选择您要查找的数据范围,然后单击 库工具 > 超级查找,请参见屏幕截图:

doc按值突出显示12

2。 在 超级查找 窗格,请执行以下操作:

  • (1.)首先,单击 价值观 选项图标;
  • (2.)从 下拉,在这种情况下,我会选择 选择;
  • (3.)从 Type 下拉列表中,选择要使用的条件;
  • (4.)然后单击 找到最适合您的地方 按钮将所有相应的结果列出到列表框中;
  • (5.)最后,单击 选择 按钮选择单元格。

doc按值突出显示13

3。 然后,一次选择了所有符合条件的单元格,请参见屏幕截图:

doc按值突出显示14

4。 现在,您可以根据需要更改所选单元格的背景颜色或字体颜色。


周末忙于工作,使用 Kutools for Excel,
给您一个轻松愉快的周末!

周末,孩子们大声疾呼要出去玩,但周围有太多工作无法陪伴家人。 太阳,沙滩和大海那么远? Kutools for Excel 帮助您 解决Excel难题,节省工作时间。

图片海滩
  •  获得晋升和增加薪水并不是遥不可及的。
  •  包含高级功能,解决应用场景,部分功能甚至节省99%的工作时间;
  •  在3分钟内成为Excel专家,并获得您的同事或朋友的认可;
  •  不再需要从Google搜索解决方案,不必再为痛苦的公式和VBA代码告别了;
  •  只需点击几下即可完成所有重复操作,解放您疲惫的双手;
  •  只有39美元,但比其他人的4000美元的Excel教程更有价值;
  •  110,000万名精英和300+家知名企业的选择;
  •  30天免费试用,并在60天内无条件全额退款;
  •  改变工作方式,然后改变生活方式!
 

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