excelcalendarconditional-formatting

Conditional Formatting to highlight current date on calendar

I'm trying to format my excel calendar to highlight the current date but I'm struggling to find a way around it because my calendar is put together using a sequence formula.

Formula applied in cell B4:

=SEQUENCE(6,7,CHOOSE(WEEKDAY(B2),1,0,-1,-2,-3,-4,-5))

enter image description here

tried using the conditional formatting for "date occurring"

Solution

  • Select the calendar grid. Make sure B4 is the active cell. Then create a new conditional formatting rule with a formula and use this:

    =B4=DAY(TODAY())

    Note that the reference to B4 in the formula does not have any $ signs, so it will be relative to each cell, not anchored in B4.

    enter image description here

    I'm in New Zealand, so here it's already July 4.

    If the date in B2 can be outside of the current month, you may also want to check the Month matches.

    =AND(B4=DAY(TODAY()),MONTH($B$2)=MONTH(TODAY()))

    Add another parameter in the AND() function to check for the current year. Note how the reference to the cell B2 is absolute, using $ signs, so it always refers to B2, for each cell in the grid.