I am having some issues with making some conditional formatting work - I'm trying to Highlight the cells in paris - H&I and J&K based on what is in cell L - If L contains the word "opening" I want J&K highlighted Green and H&I highlighted Red - If L doesn't contain the word "Opening" then I want the reverse - H&I highlighted Green and J&K highlighted Red - I've looked a various forums online and guides but none of them seem to be able to help.

(The cells I want highlighting are blank, so depending on what the outcome of L is - the colour of the cell will then inform the user what cells need to be completed)

  • It will be much easier for the volunteer contributors here to help you if you will share a copy of your spreadsheet, with the link permission set to "Anyone with the link" and "Editor."
    – Erik Tyler
    Feb 8, 2022 at 17:08

1 Answer 1


There are three things to do:

  • Add the column ranges separately rather than a single range combining both columns
    • use J1:J10,K1:K10
    • don't use J1:K10
  • Use a custom formula
  • Reference Column L as an absolute ($L1)

Rules: Columns H and I


Rules: Columns J and K




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.