I have dates in column B that include weekdays in the following format: DDD, MMM DD, YYY (For example, Sat, Jan 19, 2019).

I want each row to auto format to a specific background colour based on the weekday in column B:

  • if B1 contains Sat I want row 1 to turn blue
  • if B100 contains Sat I want row 100 to turn blue

I have tried using suggestions from these threads like regexmatch() and countif():

But neither of these seem to work for me.

I am at a loss. Any help is appreciated.

  • conditional formatting of whole row with a custom formula: =IF(LEFT($A1, 3)="Sat", 1, 0)


  • Amazing! Thank you! That works brilliantly! – Raphael Friess Jan 17 at 20:35
  • create conditional formatting based on Text starts with like:


  • This doesn't work. I need the entire row to turn Blue. Hence my mention of using Custom Formula and not Text starts with, or Text contains. – Raphael Friess Jan 17 at 18:08
  • @RaphaelFriess I see, another answer provided... cheers – MARK MY ANSWER Jan 17 at 18:41

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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