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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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