基于日期的条件格式行 [英] Conditional Formatting Rows Based on Date
问题描述
我有一个表，其中包含约500行数据。我想设置条件格式以根据行的年龄（基于该行中的日期单元格）突出显示行。想法是，随着条目的变老，它会以白色背景输入，但是随着年龄的增长，它会变成红色的深色阴影。我们有5个不同星期的数据，并计划增加更多数据。这在Excel中是可能的，还是我需要每周手动突出显示？
I have a table with a round 500 rows of data. I want to setup conditional formatting to highlight rows based upon how old a row is (based upon the date cell in that row). The idea is that as an entry get's older, it is entered with a white background, but as it ages, it turns a darker shade of red. We have data taken from 5 separate weeks and plan to add more. Is this POSSIBLE in Excel, or will I need to highlight each week manually?
推荐答案
如果您愿意只列出一列，
If you are willing to have one column be highlighted, as opposed to the entire row, this example should help.
重现步骤：
- 突出显示带有日期的列
- 在条件格式菜单中（在功能区上的主页选项卡下-Office 2013）
- 新规则
- 根据其值设置所有单元格的格式。
- 为最低值选择最深的颜色
- 为最高价值选择最浅的颜色
- 确定
- Highlight the column with the dates
- In the Conditional Formatting menu (Under the Home Tab on the Ribbon - Office 2013)
- New Rule
- Format All Cells based on their values.
- Choose the darkest color for the Lowest Value
- Choose the lightest color for the Highest Value
- OK
在下面的示例中请注意颜色的比例如何根据范围。当最低的日期是2001年时，它接收到最深的红色，但是当输入1980年的日期时，2001年及以后的日期将被认为是较亮的第三位。
Note in the example below how the scale of the colors changes based on the range. When the lowest date is 2001, that receives the darkest red, but when a date from 1980 is entered, the dates from 2001 and up are considered to be in the lighter third.
格式适用于整列，因此您可以根据需要添加任意数量的日期。
The formatting applies to the entire column, so you can add as many dates as you need.
以下是一些非常有用的技巧，它们演示了条件格式的多种用法：
Here are some very useful tips demonstrating a number of uses for conditional formatting:
< a href = https://www.ablebits.com/office-addins-blog/2014/06/17/excel-conditional-formatting-dates/ rel = nofollow noreferrer>如何有条件地格式化日期和时间Excel-内置规则和公式
这篇关于基于日期的条件格式行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
