Conditionally format a range based on a different range

Question

Given the sheet:

A| B | C = SUM($D2:$Z2) | D | E | F | ...

How would I, for a range of rows (for example: 4:50), color columns Cx:Zx (for row x) if Bx > Cx, without making a conditional rule for each individual row and/or each individual column?

(Assume there are a lot of rows and a lot of columns.)

Mashie

Answer 1

There are several Q&A on Stackoverflow that would assist you. In particular Conditional formatting based on another cell's value and the answer (with the attached comments) by Zig Mandel. It's a reminder that some Googling is always worthwhile before asking a new question.

• Select "Custom formatting" from the Format menu.
• Set "Apply to Range" to C2:Z50
• "Format cells if", select "Custom formula is" and enter =$B2:$B>$C2:$C
• Select Done.

---

B is less than C

---

B is greater than C

Screen shots

Viscera

That is true, but none answered fully with regard to upper left and lower right range bounds with end range of full column in the custom formula. Thank you; I believe that your answer is original and more robust than others provided. – Mashie

@Mashie You may be right, but I'm not sure. The BIG thing revealed in that Q&A was the use of the '$' sign in the custom formula. I had to experiment with combinations to have the condition apply to a whole row, but I haven't seen any discussion else on that topic. What I'm saying is sometimes another question may not necessarily resolve one's question, but it may provides a key; in fact, it may take a few other questions to "join the dots". – Viscera