Solved: Highlight date if it is two weeks late

[Djblois]

I want to highlight all the dates in a column that are older than 2 weeks. Here is the code I have tested:

With Range("S2", Range("S2").End(xlDown).Offset)
With .FormatConditions.Add(xlExpression, FormulaR1C1:="=ISBlank(RC)")
.ColorIndex = xlNone
End With
With .FormatConditions.Add(xlExpression, FormulaR1C1:="=((Today()-RC/7)>2")
.Font.Bold = True
.Interior.ColorIndex = 8
.ColorIndex = 15
End With
.NumberFormat = "mm/dd/yy;@"
.HorizontalAlignment = xlCenter
End With

so far it doesn't work.

[Bob Phillips]

Dim sFormula As String

sFormula = "=AND(" & ActiveCell.Address(False, False) & "<>""""," & _
ActiveCell.Address(False, False) & "<Today()-14)"
With Range("S2", Range("S2").End(xlDown).Offset)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:=sFormula
With .FormatConditions(1)
.Font.Bold = True
.Interior.ColorIndex = 8
.Font.ColorIndex = 15
End With
.NumberFormat = "mm/dd/yy;@"
.HorizontalAlignment = xlCenter
End With


But why set it with VBA?

[Charlize]

Sub checkdates()
Dim checkdates As Range
Dim checkdate As Variant
Set checkdates = Worksheets(1).Range("S2:S" & Worksheets(1).Range("S" & Rows.Count).End(xlUp).Row)
For Each checkdate In checkdates
If Date - 14 > checkdate And checkdate <> vbNullString Then
Worksheets(1).Range("S" & checkdate.Row).Interior.ColorIndex = 8
Else
Worksheets(1).Range("S" & checkdate.Row).Interior.ColorIndex = xlColorIndexNone
End If
Next checkdate
End Sub

[Kippers]

You dont need to write VBA code if you only want to change format of the cell conditional formating works fine

[Bob Phillips]

He is writing code to set conditional formatting. Which was why I asked the question that I did.

[Charlize]

Maybe that his menubar is corrupt ? Or just lazy to click a few times when you can do it with one click (for executing a macro under a button).

Charlize

[Djblois]

Sorry XLD,

I didn't see your question before but the reason why I want to use code is because it will be used in my add-in for a report that many people will be running often.

[Djblois]

Charlize,

That worked perfectly.

thank you