I am attempting to use conditional formatting to highlight all Mondays in a row in orange.

This is what I have found and attempted so far;


But none of the Mondays are highlighted (there are definitely some present in the row). How can I use conditional formatting to highlight the Mondays?

  • Are any other cell highleghter. (Not at a PC with ms office atm, but I know the first day of the week can be either Sunday or Monday. Not sure if those regional settings apply here. – Hennes Aug 11 '16 at 12:00

You've two issues in your formula:

  • your formula should refer only to one cell (the one which is active when you write your formatting)
  • default day numbering of WEEKDAY function is 1 to 7, it'll never be 0 (in default format Monday is 2)

enter image description here

  • WEEKDAY is 1 to 6? What happens to the 7th day in the week? – Chris Aug 11 '16 at 14:03

Your formula might have worked when all those days are Mondays.

In the Conditional Formatting, you need to enter the formula for one cell. It will be automatically applied to all others, relative or absolute, as requested.

So select all the cells you want it applied to, and in the decision formula, write =WEEKDAY(B1)=0 (or =WEEKDAY(B$1)=0).

To verify what I claim, you can click on for example E1, and look up the formula in the conditional formatting, and it will show =WEEKDAY(E1)=0 - as it should.

  • How do you know what cell is being considered as the basis when using A1 formatting? (This is why I prefer R1C1 formatting) – Random832 Aug 11 '16 at 19:05
  • Always the first of the cells you select.(hey I didn't code it... ) – Aganju Aug 11 '16 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.