Forcing a cell to format red when another cell's value goes below zero

[conton]

I'm using Excel 2007 on Windows XP Professional.

My workbook is very simple: one sheet contains inventories, the other contains individual orders of incoming (negative) and outgoing (positive) inventory. The idea is that the sum of all of the orders for one product will equal the remaining inventory of that product. Here is a very simplified example of what I mean:

Inventory Sheet:

Orders Sheet:

Notice how Jerry's order for 1 hat (D9) has put my hat inventory into the negative (which is fine). I want to have the text in the cell that contains the value that puts that product's inventory into the negative (in this case D9) to turn red automatically.

Hopefully this is clear enough; I will try to clarify further if needed. Thank you in advance for any advice.

 

[shg]

Welcome to the board.

In D5, use the CF formula =SUM(D$4:D5)>0 and copy the formatting down.

 

[conton]

Hi there, thanks for the quick reply!

I actually tried that, but it yields a somewhat undesired result when more inventory is added. Here's the new Orders sheet with that formatting applied:

Notice how D9 stays red, even though there has since been more inventory added, and therefore that entry no longer puts the inventory into the negative. I am trying to have only the product orders that currently put the inventory into the negative to appear red (eg. only D12 in the above example).

Hopefully there is a way to do this. Thanks again!

 

[markmzz]

Shg,

Or try this (not need to copy):

1) Select the range C:D.

2) And use CF formula =SUM(C$1:C1)>0

Markmzz

 

[markmzz]

Shg,

Or try this (for Excel 2007 or 2010):

1) Select the range C:D.

2) And use CF formula =(SUM(C$1:C1)>0)*(COUNT(C2:C$1048576)=0)

Markmzz
<!-- / message -->

 

[conton]

Hi there,

Thanks for the reply, but that didn't work for me. Still looking for a solution.

 

[markmzz]

Hi there,

Thanks for the reply, but that didn't work for me. Still looking for a solution.

Conton,

What was the error appear?

Post a example of the error.

OBS: only one correction in my solution - range C:E

Markmzz

 

[markmzz]

Conton,

Or try this (for Excel 2007 or 2010):

1) Select the range C:E.

2) And use CF formula =(SUM(C$1:C1)>0)*(COUNT($B1:$B$1048576;"INVENTORY IN")<1)

Or try this (for Excel 2003):


1) Select the range C:E.


2) And use CF formula =(SUM(C$1:C1)>0)*(COUNT($B1:$B$65536;"INVENTORY IN")<1)


Markmzz <!-- / message -->

 

[markmzz]

Conton,

Sorry, missed IF of the COUNTIF.

Now is correct.

Or try this (for Excel 2007 or 2010):

1) Select the range C:E.

2) And use CF formula =(SUM(C$1:C1)>0)*(COUNTIF($B1:$B$1048576,"INVENTORY IN")<1)

Or try this (for Excel 2003):

1) Select the range C:E.

2) And use CF formula =(SUM(C$1:C1)>0)*(COUNTIF($B1:$B$65536,"INVENTORY IN")<1)

Markmzz <!-- / message -->