VBA 插入 if - 根据日期更改单元格填充
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11525252/
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
VBA insert if - Change cell fill based on date
提问by Liam Coates
Trying to do an insert if formula in VBA for the following;
尝试在 VBA 中插入 if 公式以进行以下操作；
In my column K I want three conditions:
在我的专栏 KI 中需要三个条件：
- Date is Today or older (ie project is due today or earlier) = RED
- Date is Today + up to 7 days = Amber
- Date is Today less more than 7 days = Green
- 日期是今天或更早（即项目今天或更早到期）= RED
- 日期是今天 + 最多 7 天 = 琥珀色
- 日期是今天少于 7 天 = 绿色
I was thinking of using something along the lines of:
我正在考虑使用以下内容：
Sub ChangeColor()
lRow = Range("K" & Rows.Count).End(xlUp).Row
Set MR = Range("K3:K" & lRow)
For Each cell In MR
If cell.Value = "TODAY" Then cell.Interior.ColorIndex = 10
If cell.Value = "TODAY-7days" Then cell.Interior.ColorIndex = 9
If cell.Value = "Morethan7Days" Then cell.Interior.ColorIndex = 8
Next
End Sub
I've been trying but I'm not sure how to do it.
我一直在尝试，但我不知道该怎么做。
I think my way is correct yet I am not sure how to code the If date=-7days then and so on.
我认为我的方法是正确的，但我不确定如何编码 If date=-7days then 等等。
Can someone provide some guidance? :)
有人可以提供一些指导吗？:)
采纳答案by Dick Kusleika
VBA has a Date function that returns today's date. Dates in VBA are the number of days since December 31, 1900 (usually and with a leap year bug), so you can subtract or add integers to Date to get past and future days.
VBA 有一个 Date 函数，可以返回今天的日期。VBA 中的日期是自 1900 年 12 月 31 日以来的天数（通常还有闰年错误），因此您可以对 Date 减去或添加整数以获取过去和未来的日期。
Sub ChangeColor()
Dim rCell As Range
With Sheet1
For Each rCell In .Range("K3", .Cells(.Rows.Count, 11).End(xlUp)).Cells
If rCell.Value <= Date Then
rCell.Interior.Color = vbRed
ElseIf rCell.Value <= Date + 7 Then
rCell.Interior.Color = vbYellow
Else
rCell.Interior.Color = vbGreen
End If
Next rCell
End With
End Sub
回答by Sorceri
Mr. Anderson is correct that you could accomplish this with conditional formatting however if you want to do this in VBA Create a variable to hold the date and set it to the current day minus the time. Then you just want to Format the cells value to a date format. Once this is done you can use dateAdd to and and subtract the days. See below
安德森先生是正确的，您可以使用条件格式来完成此操作，但是如果您想在 VBA 中执行此操作，请创建一个变量来保存日期并将其设置为当前日期减去时间。然后您只想将单元格值格式化为日期格式。完成此操作后，您可以使用 dateAdd 并减去天数。见下文
Sub ChangeColor()
Dim myDate As Date
'format the date excluding time
myDate = FormatDateTime(Now, 2)
lRow = Range("K" & Rows.Count).End(xlUp).Row
Set MR = Range("K3:K" & lRow)
For Each cell In MR
If FormatDateTime(cell.Value, 2) = myDate Then cell.Interior.ColorIndex = 10
If FormatDateTime(cell.Value, 2) = DateAdd("d", -7, myDate) Then cell.Interior.ColorIndex = 9
If FormatDateTime(cell.Value, 2) = DateAdd("d", 7, myDate) Then cell.Interior.ColorIndex = 8
Next
End Sub
I did notice that your checking to see if it is equal so only dates that are exactly Todays date, 7 days from today and 7 days previous to today will have the interior color filled. greater than and less than to fill all interior cell colors
我确实注意到您检查它是否相等，因此只有恰好是今天的日期、从今天起 7 天和今天之前 7 天的日期才会填充内部颜色。大于和小于填充所有内部单元格颜色
Sorry for all the edits
对不起所有的编辑
