Use FormatConditions.Add FormatConditions is a property of the Range object. Add has the following parameters. The FormatConditions collection can only hold 3 FormatConditions.
FormatConditions.Add(Type, Operator, Formula1, Formula2)
To apply formatting to values within a specific date period use xlTimePeriod in the Type parameter and then use the DateOperator parameter to specify the date period – options shown in the table below.
| Name | Value | Description |
|---|---|---|
| xlLast7Days | 2 | Last 7 days |
| xlLastMonth | 5 | Last month |
| xlLastWeek | 4 | Last week |
| xlNextMonth | 8 | Next month |
| xlNextWeek | 7 | Next week |
| xlThisMonth | 9 | This month |
| xlThisWeek | 3 | This week |
| xlToday | 0 | Today |
| xlTomorrow | 6 | Tomorrow |
| xlYesterday | 1 | Yesterday |
Sub formatlastweek()
Dim rg As Range
Set rg = Range("H5", Range("h5").End(xlDown))
Dim cf As FormatCondition
Set cf = rg.FormatConditions.Add(Type:=xlTimePeriod, DateOperator:=xlLastWeek)
cf.Interior.Color = rgbDarkGray
End Sub
The code above relates to the conditional formatting displayed below.
