2

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.)

pnuts
  • 58,317
  • 11
  • 87
  • 139
kando
  • 441
  • 4
  • 16

1 Answers1

6

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 less than C

B is greater than C
enter image description here

Screen shots

Tedinoz
  • 5,911
  • 3
  • 25
  • 35
  • 1
    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. – kando Jan 04 '19 at 16:32
  • @kando 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". – Tedinoz Jan 05 '19 at 19:48