vba 根据开始和结束日期格式化单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13568429/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Format cells based on start and end date?
提问by
I have two columns, Startand Finish(both dates), that I would like to utilize for conditional formatting. To the right of the Start and Finish columns, I have, essentially, a calendar, with each column representing a month (e.g. J for January, F for February, M, A, etc.). In this calendar, I'd like to fill the two cells that indicate the start and finish date. For example, if my Startvalue was Jan 2013 and my Finishvalue was March 2013, it would apply the fill to those respective cells.
我有两列Start和Finish（两个日期），我想将它们用于条件格式。在 Start 和 Finish 列的右侧，我基本上有一个日历，每列代表一个月（例如，J 代表一月，F 代表二月，M、A 等）。在此日历中，我想填充表示开始日期和完成日期的两个单元格。例如，如果我的Start值为 2013 年 1 月，而我的Finish值为 2013 年 3 月，它会将填充应用于相应的单元格。
I have explored the "Conditional Formatting" menu, and I assume I have to use a formula, but I'm not really sure how to implement a formula. Is this the best method? Are there any other ways? Would VB simplify this task?
我已经探索了“条件格式”菜单，我假设我必须使用一个公式，但我不确定如何实现一个公式。这是最好的方法吗？还有其他方法吗？VB 会简化这个任务吗？
I assume that I could apply conditional formatting to each cell, something like the following pseudocode:
我假设我可以对每个单元格应用条件格式，类似于以下伪代码：
=IF(startDate is January 2013) then use the formatting defined in this dialog box
=IF(startDate is February 2013) then use the formatting defined in this dialog box
And so on for each date. However, I'm wary of the syntax. Per Tech on the Net, it defines the =IFsyntax as follows:
每个日期依此类推。但是，我对语法持谨慎态度。Per Tech on the Net，它定义的=IF语法如下：
IF( condition, [value_if_true], [value_if_false] )
Clearly, the conditionis matching the dates, whereas the value_if_true and value_if_false don't really make sense to me, because I'm not returning a value, I'm only formatting the cells conditionally.
显然，这condition是匹配日期，而 value_if_true 和 value_if_false 对我来说没有意义，因为我没有返回值，我只是有条件地格式化单元格。
Thank you!
谢谢！
Update: Here is a picture of the general layout, that is, there is a row for each task, which corresponds to the calendar.
更新：这里是大体布局图，也就是每个任务有一行，对应日历。
Here is the formula I'm trying to use for conditional formatting:
这是我试图用于条件格式的公式：
=MONTH($B6)=COLUMNS($D6:AM6)
I think I need to setup my column headers for months differently. Right now, they are just single letters as text for brevity sake, but I'll enter a date and format the headers properly. Will update with progress.
我想我需要以不同的方式设置我的列标题几个月。现在，为简洁起见，它们只是单个字母作为文本，但我将输入日期并正确格式化标题。会随着进度更新。
采纳答案by barry houdini
Are you saying there's just a single cell for each month? If so then assuming start date in A2, end date in B2 then 12 month cells in D2:O2 then try this:
你是说每个月只有一个单元格吗？如果是这样，那么假设 A2 中的开始日期，B2 中的结束日期，然后 D2:O2 中的 12 个月单元格，然后试试这个：
Select D2:O2 and apply this formula in conditional formatting
选择 D2:O2 并在条件格式中应用此公式
=MONTH($A2)=COLUMNS($D2:D2)
=MONTH($A2)=COLUMNS($D2:D2)
apply required format > OK
应用所需格式 > 确定
回答by LIKA
I HAVE BEEN TRYING TO FORMAT DATE TO BE LIKE THIS (M/D/Y) I DO A CALCULATION IN EXCEL IN PIVOT SO I TY=RY TO FIND THE AGE OF EVERYONE BUT SOME ARE CORRECT AND SOME ARE ERROR SO I DONT NEED ERROR BCAUSE ITS AFFECT THE CALCULATION...
我一直在尝试将日期格式化为这样 (M/D/Y) 我在 PIVOT 中使用 EXCEL 进行计算，所以我 TY=RY 来找到每个人的年龄，但有些是正确的，有些是错误的，所以我不需要错误因为它会影响计算......
