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.
我对 VBA 很陌生,所以我似乎不太清楚如何做到这一点,但在我能够很好地学习 VBA 来编写代码之前需要宏。有人介意向我展示一个如何做到这一点的例子吗?
谢谢。
这应该会让你走上正轨!
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
将代码复制到 Visual Basic 编辑器 (ALT+F11) 中的新代码模块。请注意,您可能需要更改“;”到“,”取决于您的本地化!您可以将范围切换到需要格式化的范围,并修改示例公式以满足您的需求或创建新公式。
您可以找到颜色索引here以及有关制作实际公式的信息here.
HTH
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)