Excel条件格式化宏 [英] Excel Conditional Formatting macro

问题描述

我正在尝试在excel中编写一个宏来做一些平凡的任务，需要做的excel。我需要一个宏，它将根据值范围内的日期有条件地格式化一系列值。它需要是动态的，因为范围每次运行时都会改变大小。我已经附上了最后的表格应该是什么样的图片，以及为什么这样格式化的原因。

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来编写代码。有人会告诉我一个如何做到这一点的例子？
谢谢。

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.

推荐答案

这应该让你在正确的轨道上！

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

将代码复制到Visual Basic编辑器（ALT + F11）中的新代码模块。
请注意，您可能需要更改;到，取决于您的本地化！您可以将范围切换到您需要格式化的范围，并修改样品公式以满足您的需要或创建新的样品。

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.

您可以在这里找到ColorIndexes 此处以及有关制作实际公式的信息here

You can find the ColorIndexes here and info about crafting the actual formulas here.

HTH

这篇关于Excel条件格式化宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！