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.

2 Answers 2

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


  • Amazing! Thank you! That works brilliantly! Jan 17, 2019 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. Jan 17, 2019 at 18:08
  • @RaphaelFriess I see, another answer provided... cheers
    – user0
    Jan 17, 2019 at 18:41

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.