Conditional formatting for current week's dates.

[PierreR]

The large block of coding below successfully conditionally formats the StartDate field's backcolor to yellow from the beginning of the current month to the current date.

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
    With StartDate
        With .FormatConditions
        .Delete
        Set fcd = .Add(acFieldValue, acBetween, _
            "#" & Format(DateSerial(Year(Date), Month(Date), 1), "mm-dd-yyyy") & "#" _
            , "#" & Format( _
            Date, "mm-dd-yyyy") & "#")
        fcd.BackColor = vbYellow
        End With
    End With
End Sub

And if you want to know how to get some other dates replace the following snippets into above coding.

Last day of the month

DateSerial(Year(Date), Month(Date) + 1, 0)

First day of next month

DateSerial(Year(Date), Month(Date) + 1, 1)

Last day of next month

DateSerial(Year(Date), Month(Date) + 2, 0)

…and so on.


WHAT I’D LIKE TO KNOW IS HOW TO DO THIS FOR THE FIRST DAY OF THE WEEK TO THE LAST DAY OF THE WEEK.

 

[PierreR]

Solution

Okay, I adapted the code to have the backcolor of the StartDate object of the form yellow only for the current week, Monday to Sunday. If you want it only until Friday, change the 8 to a 6.

Private Sub Form_Open(Cancel As Integer)
Dim fcd As FormatCondition
    With StartDate
        With .FormatConditions
        .Delete
        Set fcd = .Add(acFieldValue, acBetween, _
        "#" & Format(Date - Weekday(Date) + 2, "mm-dd-yyyy") & "#", _
        "#" & Format(Date - Weekday(Date) + 8, "mm-dd-yyyy") & "#")
        fcd.BackColor = vbYellow
        End With
    End With
End Sub