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?

On Sat, 29 Nov 2003 23:26:26 +1100, "DL" wrote:

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?



Well, a formula which will compute the first Tuesday in any given month is:


=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),2,1,0,6,5,4,3)

So you could use Conditional Formatting. You could certainly use VBA to apply
conditional formatting to the range in which your calendar exists.

In VBA code it might be something like:


calendar.FormatConditions.Delete
calendar.FormatConditions.Add Type:=xlExpression, Formula1:= _

"=A1=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DAT E(YEAR(A1),MONTH(A1),1)),2,1,0,6,5,4,3)"
calendar.FormatConditions(1).Interior.ColorIndex = 15

where 'calendar' represents the range where the calendar is stored.






--ron

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?

Formula can be abbreviated to :

=DAY(A1)+WEEKDAY(A1-DAY(A1)+5)=8

So this new formula can replace the old one in PutCondFormatviaVBA()


Accordingly, in the HighLight1stTuesdayOfMonth(),
Replace:
If C = C - Day(C) + 8 - Weekday(C - Day(C) + 5) Then
By:
If Day(C) + Weekday(C - Day(C) + 5) = 8 Then

Regards,

Daniel M.

Daniel,
Sub PutCondFormatviaVBA.
That's a fabulous little macro. Thanks a million.
--
Regards,
David Lindstrom


"Daniel.M" wrote in message
...
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?