conditional format

M

Manhar

Cells C1:C4 contain up to 4 amounts.
Cells E1:E4 contain up to 4 amounts.
Cells B1:B4 contain up to 4 amounts, the total of which
is the sum of (C1:C4+E1:E4).

Cell A1 contains a description of the what the above
amounts refer to. (e.g. "Apt 203").

If the total of amounts in B1:B4 does NOT agree, I want
cell A1 to return a red background.

Please help! Thanks!
 
J

Jason Morin

It appears that E1:E4 is keyed in, not an actual sum
formula that is adding C1:C4 with E1:E4. If that's the
case, then select A1 and under conditional formatting:

=SUM(B1:B4)<>SUM(C1:C4,E1:E4)

and format as desired.

HTH
Jason
Atlanta, GA
 
G

Guest

Thanks for the tip. That worked!
Now, a follow-up question:

With the conditional formatting, if the amount is not
correct, the background in cell A1 (which contains "Apt
203") returns a red background.

Further down the sheet, there is cell A6 (Apt 204), with
related cells as in my original question ( this time
C6:C9, E6:E9, B6:B9).

How do I copy the conditional format to A6 to return the
same result as A1, without affecting the "Apt 204" entry?

thanks!
 
J

Jason Morin

You can copy cell A1, select A6, and then do Edit > Paste
Special > Formats.

Or, what I would do is simply click A1, click your
Paintbrush icon, and click on A6. Much quicker.

HTH
Jason
Atlanta, GA
 
D

David McRitchie

The Conditional Format in Cell A1 the only cell to be
colored is =SUM(B1:B4)<>SUM(C1:C4,E1:E4)

If that C.F. is copied as the C.F. for Cell A6 then
the formula will be =SUM(B6:B9<>SUM(C6:C9,E6:E9)

Hope that is what you are expecting.

More information on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Coloring will be to the cells selected, and each cell in the selection
will have a formula based on adjustments from the formula in the
active cell when the C.F. is assigned. When you copy the format
same applies new cell's C.F. is based on the formula in the pattern
you are painting from.
 
D

David McRitchie

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

David McRitchie said:
The Conditional Format in Cell A1 the only cell to be
colored is =SUM(B1:B4)<>SUM(C1:C4,E1:E4)

If that C.F. is copied as the C.F. for Cell A6 then
the formula will be =SUM(B6:B9<>SUM(C6:C9,E6:E9)

Hope that is what you are expecting.

More information on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Coloring will be to the cells selected, and each cell in the selection
will have a formula based on adjustments from the formula in the
active cell when the C.F. is assigned. When you copy the format
same applies new cell's C.F. is based on the formula in the pattern
you are painting from.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Jason Morin said:
You can copy cell A1, select A6, and then do Edit > Paste
Special > Formats.

Or, what I would do is simply click A1, click your
Paintbrush icon, and click on A6. Much quicker.

HTH
Jason
Atlanta, GA
Click to expand...
Click to expand...
 
Top