vba Excel 条件格式宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24112764/
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
Excel Conditional Formatting macro
提问by user2679225
I'm trying to write a macro in excel to do some mundane task that I need to do in excel. I need an macro that will conditionally format a ranges of values based on the date which is inside the range of values. It needs to be dynamic since the range changes size every time I run. I've attached a picture of what the final sheet should like like with a comment of the reason why it is formatted that way.
我正在尝试在 excel 中编写一个宏来完成一些我需要在 excel 中完成的平凡任务。我需要一个宏，它将根据值范围内的日期有条件地格式化一系列值。它需要是动态的，因为每次运行时范围都会改变大小。我附上了一张关于最终工作表应该是什么样子的图片，并附上了为什么以这种方式格式化的原因的评论。
I'm very new to VBA so I can't quite seem to figure out how to do this, but need the macro before I will be able to learn VBA well enought to code this up. Would someone mind showing me an example of how this could be done? Thanks.
我对 VBA 很陌生，所以我似乎不太清楚如何做到这一点，但是在我能够很好地学习 VBA 来编写代码之前需要宏。有人介意给我看一个如何做到这一点的例子吗？谢谢。
回答by natancodes
This should get you on the right track!
这应该让你走上正轨！
Sub Main()
'---Variables---
Dim myRange As Range
'---Customize---
Set myRange = ThisWorkbook.Sheets(1).Range("A:D") 'The range to be formatted
'---Logic---
myRange.FormatConditions.Delete 'Clear
'Rules that are up in the list have higher priority
Call FormatRange(myRange, 3, "=AND($D1<TODAY()-2;NOT(ISBLANK($D1)))")
Call FormatRange(myRange, 29, "=AND($D1<TODAY()-1;NOT(ISBLANK($D1)))")
Call FormatRange(myRange, 45, "=AND($D1<TODAY();NOT(ISBLANK($D1)))")
Call FormatRange(myRange, 10, "=$D1=TODAY()")
'Note that you may have to use , instead of ; depending on your localization!
'You can find ColorIndexes from http://dmcritchie.mvps.org/excel/colors.htm
End Sub
'A support method that makes creating new conditional formats a little easier
Public Sub FormatRange(r As Range, colorIndex As Integer, formula As String)
r.FormatConditions.Add xlExpression, Formula1:=formula
r.FormatConditions(r.FormatConditions.Count).Interior.colorIndex = colorIndex
End Sub
Copy the code to a new code module in the Visual Basic editor (ALT+F11). Note that you may have to change the ";" to a "," depending on your localization!You can switch the range to the one you need to format and either modify the sample formulas to suit your needs or create new ones.
将代码复制到 Visual Basic 编辑器中的新代码模块 (ALT+F11)。 请注意，您可能需要更改“;” 到“，”取决于您的本地化！您可以将范围切换到您需要格式化的范围，并修改示例公式以满足您的需要或创建新公式。
You can find the ColorIndexes hereand info about crafting the actual formulas here.
您可以在此处找到 ColorIndexes ，并在此处找到有关制作实际公式的信息。
HTH
HTH
