Highlight Cells Using Conditional Formatting Based on Other Cells Value

Question

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)

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

---

Example