vba 根据日期和其他条件突出显示单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15670553/
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
Highlight cells based on date and other conditions
提问by user2214690
I am trying to code in VBA a highlighting function based on certain conditions. I can easily do this via conditional formatting, but I have noticed that if a user cuts/pastes (other than special)/deletes rows/etc. then the conditional formatting ranges are modified. I want the conditional formatting ranges to stay fixed rather than mapped to the actual cells. If anyone knows how to do that, or protect the conditional formatting but still allow data manipulation, then this code would be unnecessary.
我正在尝试在 VBA 中编写基于某些条件的突出显示功能。我可以通过条件格式轻松地做到这一点，但我注意到如果用户剪切/粘贴（特殊除外）/删除行/等。然后修改条件格式范围。我希望条件格式范围保持固定而不是映射到实际单元格。如果有人知道如何做到这一点，或者保护条件格式但仍然允许数据操作，那么此代码将是不必要的。
I have found two different codes that I have been trying but since I'm new to VBA, I'm not very good at it and run into problems. I don't know how to use the Isblank or Isempty feature for one.
我发现了两种不同的代码，我一直在尝试，但由于我是 VBA 的新手，我不太擅长它并遇到问题。我不知道如何使用 Isblank 或 Isempty 功能。
I need to highlight dates that are earlier than 30 days from now (including dates passed) in red. I need to highlight dates that are earlier than 60 days from now but more than 30 in yellow. Cells without data and cells beyond 60 days out must remain unhighlighted.
我需要用红色突出显示早于 30 天的日期（包括过去的日期）。我需要用黄色突出显示早于 60 天但超过 30 天的日期。没有数据的单元格和超过 60 天的单元格必须保持不突出显示。
Any help is greatly appreciated!
任何帮助是极大的赞赏！
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("C3:T65")) Is Nothing Then
Select Case Target
Case Is <= Date + 60
icolor = 6
Case Is <= Date + 30
icolor = 3
Case IsEmpty()
icolor = 2
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
Other option:
其他选择：
Sub Highlight()
Dim cell As Range
For Each cell In Range("C3:T65")
If cell.Value <= Date + 60 And cell.Value > Date + 30 Then
cell.Offset(0, 1).Interior.ColorIndex = 6
ElseIf cell.Value <= Date + 30 Then
cell.Offset(0, 1).Interior.ColorIndex = 3
ElseIf cell.Value IsEmpty() Then
cell.Offset(0, 1).Interior.ColorIndex = 2
End If
Next cell
End Sub
采纳答案by Peter Albert
Both your codes are almost there. The following combination of the two should do the job:
你的两个代码都差不多了。两者的以下组合应该可以完成这项工作：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim cell As Range
If Intersect(Target, Range("C3:T65")) Is Nothing Then Exit Sub
For Each cell In Target
icolor = 0
Select Case cell
Case Is <= Date + 30: icolor = 3
Case Is <= Date + 60: icolor = 6
Case "": icolor = 2
End Select
If icolor <> 0 Then cell.Interior.ColorIndex = icolor
Next cell
End Sub
回答by Steven Marciano
Check the cell is null using VBNullString:
使用 VBNullString 检查单元格是否为空：
ElseIf cell.Value = vbNullString Then
