Not sure if my brain is frazzled after a long day but I just can't get this issue with conditional formatting fixed...

I have created a calendar in Excel which auto-populates week days based on the year (also accounts for leap years too) though I am trying to get the table cells to turn a different colour if it happens to be a weekend. The month's days are typically populated by the formula =TEXT(WEEKDAY(DATE(CalendarYear,1,n),1),"aaa") where n is the relative day in the month.

Each month is in its own tab/sheet.

To highlight the weekend, I am currently using the simple formula =OR(B2="Sat",B2="Sun") - this is applied to the whole table contents =$B$4:$AF$60 BUT only the first row (row 4) seems to actually work - see image below.

Annoyingly everything else works fine bar this one bit and I just can't process why this is happening... The days will vary based on the year value; if I alter the year value row 4 works as expected with the greyed blocks shifting with the weekend days.

Any pointers/help is greatly appreciated.

Thanks

CodePudding user response：

Change your formula from:

=OR(B2="Sat",B2="Sun")

into:

=OR(B$2="Sat",B$2="Sun")

Otherwise, it will point at "B3", "B4", ...