Conditional Formatting based on formulas

[hunkietechie]

Hi All,

I have been using excel for a while now but recently started really exploring the true potential of it. I am trying to prepare a planning spreadsheet but facing an issue. I want to do a sum of all the cells in a row and compare that with the value in a single cell. If both are equal then display a certain colour else display a different colour. I am able to do this for a single row but is there a formula which does this for multiple rows together.

Example:
If K15 = sum of N15:AA15 fill the cell K15 with green colour else red
If K16 = sum of N16:AA16 fill the cell K16 with green colour else red
If K17 = sum of N17:AA17 fill the cell K17 with green colour else red

cells K15, 16, 17 etc...should not have any colour if no value is entered
btw, I use excel 2007 if that makes any difference.

Thanks in advance!

Cheers

[NBVC]

Select cell K15:K17 and then go to Home|Conditional Formatting, Manage Rules.

Select New Rule and then select Use formula to determine which cells to format and then select Cell Value >> Equal to >> enter: =K15=SUM(N15:AA15)

click Format and choose Green from Fill tab.

[hunkietechie]

Thanks for the quick reply but it formats only cell K15 even if I select K15:K17 before clicking on conditional formatting. If I enter values in rows 16 and 17, nothing happens in K16 and K17 respectively!

[NBVC]

It worked in my test...

The SUM() formulas are relative to the cell selections.

Reinvoke conditional formatting on those cells and check that the applies to box has: =$K$15:$K$17 in it.

Also look at the formula again and make sure there are no quotes around it... and that you have no $ signs in them...

[hunkietechie]

Thanks again...the $ sign was the culprit!