Excel Conditional Formatting macro

4
votes

enter image description hereI'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.

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.

1
vbaexcel
Try recording a macro while setting up the CF rules. Then try editing the code. – Tim Williams
Conditional formatting using 3 conditions in Macro/VBA might give you a hint on how to do it programmatically. As for getting the dynamic range, you can see many examples here. – L42

1 Answers

12
votes

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.

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

HTH