I have created a weekly tracker on Google Sheets, with the days organized in columns K through AI. Each day has three columns dedicated to tasks, assigned personnel, and notes. I have successfully applied a conditional formatting rule to the range L6:O36, which colors the rows based on the assigned person.

However, when I attempt to apply the same rule to the range P6:S36, it does not work. I'm puzzled as to why this is happening. Could you please help me understand the reason behind this issue?

Here is a link to a copy of the spreadsheet: [1]: https://docs.google.com/spreadsheets/d/1eoDD7ODEnBvUCANEaGuJcwCY93DTjuMiDvmQ71G0QY4/edit#gid=1816207673

  • Welcome to Web Applications Stack Exchange. The question lacks details. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Jul 13 at 10:27
  • For some clues, see this explanation of how absolute and relative addresses work in a conditional formatting custom formula rule. Jul 13 at 10:28
  • Please take the Tour and review How to Ask. This question has helpful comments that have not yet been addressed. I recommend you review the comments and edit your question accordingly. Do not create duplicate questions. The duplicate created yesterday has been removed.
    – Blindspots
    Jul 19 at 16:13

1 Answer 1


Your existing conditional formatting rules are hard-coded to refer to column L6:L36 and will therefore not work in the columns to the right or the rows to the bottom.

To make it work, apply your conditional formatting custom formula rules to the open-ended range L6:AI and modify the rules like this:

="Sarah" = offset(L6, 0, -mod(column() - column($L$1), 4))

See your sample spreadsheet.

  • @doubleunary- thank you! Is it possible to add another custom rule that will return the range back to white with a strike through applied if the status is marked as "Complete"?
    – Tanisha
    Jul 20 at 18:08
  • Yes. Please ask only one question per post. Post a new question instead. Jul 20 at 18:24

Your Answer

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

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