Color a cell where the sum of the row reaches a certain value

Question

I need to find out in which week a sum of working hours exceeds a certain amount of hours. I would like to color that certain cell.

Thanks in advance! Tim

Answer 1

Conditional formatting is what you need. Simply select the whole row 3 and give it a try:

Conditional formatting, entire row based

Answer 2

Select B3:K3, starting from B3 (This is important because the conditional formatting requires relative references, and the formula is based on which cell is Active within the Selection)

Then Select Home > Conditional Formatting > New Rule

Choose "Use a formula to determine which cells to format"

Enter: =AND(SUM($B$3:B3)>100,SUM($A$3:A3)<=100)

Click Format and then go to Fill tab to set the formatting you want for the highlighted cell.

Click OK to close everything.

Answer 3

This should do the trick for you:

• in your example, click on cell B3

• on the Home tab, click Conditional Formatting, and then New Rule

• click Use a formula to determine which cells to format

• in the Formula box enter: =(SUM($B3:B3)>100)

• click Format, and in the Font tab, choose the color you want cell to change to

• click OK, OK

• right click cell B3 and choose Copy

• highlight the range of cells B3 to K3

• right click the highlighted cells and choose Paste Special

• choose Formats and then click OK

Now, the cell that "made" the sum exceed 100 (and cells after that one) will change color depending on the values of the cells. Note that the formula for Conditional Formatting automatically updated when we copied it, partly because of the Absolute vs Relative $ references in the formula.

Answer 4

If you only want to colour the cell where the sum of working hours exceeds 100 and not the cells to the right of it, would need

=AND(SUM($B3:B3)>100,SUM($B3:B3)-B3<=100)