Good day everyone, I need to dynamically color rows of a table, until the sum of cells of a column reachs a certain value


The "Effort" column is column E. I need therefore need to color rows until the sum of column E value is 240. In this case, assuming that "Feature O" cell is in row 4, I would need to color until row 14 "Feature E" excluded. This table is subject to changes, as new rows might be added, or effort recalculated.

I hope it is clear enough so that you can help me.

Cheers. Tommaso

Gents, your answer are very much appreciated. Assuming now that I have a pool of resources available for a certain release. I need to color green up to a certain value, then, from there, need to color until another value is reached (a delta), and so on. enter image description here

What I want to reach is to show how many items fits in a certain pool of resources, with the value on coloum K

I've tried the following, but after the green color, the formula is not correct: while the pool for the 2nd release is 153.6, the formatting colors the cells up to 168, instead of 153.6

enter image description here

I hope it's clear,


Here is an example on the assumption you need to color the cells unless the total sum would go above 240:

enter image description here

  • Select range E4:E16
  • New conditional formatting rule by formula:

  • Choose format and apply

Now upon editing or adding:

enter image description here

  • 1
    Works like charm! Thanks! – Tommaso Aug 14 '19 at 12:25
  • @Tommaso, glad it works, kindly upvote/accept as answer as described here if this has been usefull. – JvdV Aug 14 '19 at 12:28
  • Done, but unfortunately I don't have 15 reputation, so my vote is not properly shown T.T – Tommaso Aug 14 '19 at 12:33

Since you are using TABLE therefore my approach is quite different to highlight rows, until the SUM is equals to certain value.

enter image description here

How it works:

  • You need to create one Helper Column.
  • Enter Criteria 240 in Cell N1.
  • Formula in Cell N2, fill it down:


  • Select entire Range I2:N14.

  • Reach to Conditional Formatting, New Rule and apply this formula.


  • Apply an appropriate Color and finish with Ok.

  • Then click Conditional Formatting then Manager Rules.

  • Now using Applies To Combo box, select Range J2:J14, then hit Apply & finish with Ok.

enter image description here

  • You get the effects on Column J.


  • Using Cell N1 to match with criteria makes the exercise dynamic.

If next is to highlight rows, until the SUM is equals to 14 or any value, then instead of redraw entire exercise, only replace the match value in cell N1, which saves the time too.

enter image description here

  • Later on for neatness you may hide Range N2:N14 as well as adjust other parameters also.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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