Excel Conditional Formatting Range of Cells 1

[grnzbra]

I have a spreadsheet which has three columns into which values are entered. A fourth column cells have the sum values of the corresponding cells in the first two columns. The values entered into the third should be the same as the values in the fourth column (which is the sum of the values of the cells in the first two columns)

I would like to highlight the cells in the third column which do not match the values in the corresponding cells in fourth column. Using conditional formatting I can set up a cell in column 3 to highlight if it doesn't match the corresponding cell in column 4. But I have about a hundred rows that must be compared.

Must I create a new rule for each row or is there some way of making a range of cells be compared with the corresponding cells in another range of cells? The closest I have been able to come to this is to have the values of a range of cells be compared to the value of a single cll in the second range.

 

[SkipVought]

hi,

This can be done in one of two ways. 1) select ALL the cells in ALL the rows that you want to CF and do the CF for the FIRST ROW. The CF will be inherited by ALL the cells selected. 2) since you already have do the CF for ONE row, a) SELECT the CFed cells in that row, b) hit the Format Painter ICON, c) select ALL the cells you want to CF. CAVEAT: The Format Painter picks up ALL formats from your original selection and propagates them thru your subsequent selection of all rows, not JUST the CF.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[grnzbra]

Thank you for the suggestions.

The first method didn't work. The format was inherited by each cell in the range, but the comparison was always with the same cell as the comparison of the first row.

I'm ready to try method 2, but am not sure what the Format Painter Icon is (I'm using Excel 2007) or where it's found.

 

[grnzbra]

OOPS! Method 1 worked. I forgot to take out the $ signs from the cell reference.


Still, what is the Format Painter Icon?

 

[SkipVought]

but the comparison was always with the same cell as the comparison of the first row.

That is due to the way that you REFERENCED the cell. You originally used an ABSOLUTE reference, which would not change as you copied the format.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}