first Tuesday in the month
Hi,
For any date in A1, the following formula returns TRUE if it's first Tuesday of
the month.
=A1=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+5)
So, via direct formatting :
Public Sub TestHighLight()
HighLight1stTuesdayOfMonth Range("A1:A300") ' or whatever
End Sub
Public Sub HighLight1stTuesdayOfMonth(Rng As Range)
Dim C As Range
For Each C In Rng
If IsDate(C) Then
If C = C - Day(C) + 8 - Weekday(C - Day(C) + 5) Then
C.Interior.ColorIndex = 3
End If
End If
Next C
End Sub
Or via conditional formatting (preferred way):
Sub PutCondFormatviaVBA()
With Range("A1:A300") ' or whatever
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=A1=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+5)"
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub
Regards,
Daniel M.
"DL" wrote in message
...
I want to use a vba procedure to examine the dates in a calendar and
highlight all those that are the first Tuesday in the month.
Can this be done?
|