I really Need Help with Conditional Formatting

[BrickCityDevil]

I have an Excel sheet used for tracking Inventory and I want to be able to monitor all changes in Inventory. I have a column for Quantity Received and another Column Quantity Signed out. I would like for if either Column is Greater than zero to issue a statement in another cell or on another page or worksheet (either one is acceptable) the current date, Item Description, "Quantity Received" or "Quantity Signed out" that value and the Quantity on hand. If there is no change then I dont want a message to be issued.
I have a cloumn that already calculates the value on hand given the quantity received or the quantity signed out.
Here is an example of the lay out.(Item Description is Column B and so forth)
<TABLE style="WIDTH: 878pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1168 border=0><COLGROUP><COL style="WIDTH: 181pt; mso-width-source: userset; mso-width-alt: 8813" width=241><COL style="WIDTH: 145pt; mso-width-source: userset; mso-width-alt: 7058" width=193><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" span=2 width=73><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><TBODY><TR style="PAGE-BREAK-BEFORE: always; HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 181pt; BORDER-BOTTOM: red 1.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: yellow" width=241 height=22>ITEM DESCRIPTION</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 145pt; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: yellow" width=193>PART NUMBER</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: yellow" width=73>MAKE</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: yellow" width=73>INITIAL QTY</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 102pt; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: yellow" width=136>QUANTITY RECEIVED</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 90pt; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: yellow" width=120>QTY SIGNED OUT</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: yellow" width=101>QTY ON HAND</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: yellow" width=86>UNIT COST</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 109pt; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: yellow" width=145>TOTAL VALUE ON HAND</TD></TR><TR style="HEIGHT: 21pt" height=28><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21pt; BACKGROUND-COLOR: #d8d8d8" colSpan=9 height=28>Location 4 C 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #3f3f3f 0.5pt solid; BORDER-TOP: #3f3f3f 0.5pt solid; BORDER-LEFT: #3f3f3f 0.5pt solid; BORDER-BOTTOM: #3f3f3f 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #f2f2f2" height=20>Inside Door Handles Right</TD><TD class=xl66 style="BORDER-RIGHT: #3f3f3f 0.5pt solid; BORDER-TOP: #3f3f3f 0.5pt solid; BORDER-LEFT: #3f3f3f; BORDER-BOTTOM: #3f3f3f 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">19552-01</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">23</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">23</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">$ 26.00 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$598.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #3f3f3f 0.5pt solid; BORDER-TOP: #3f3f3f; BORDER-LEFT: #3f3f3f 0.5pt solid; BORDER-BOTTOM: #3f3f3f 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #f2f2f2" height=20>Inside Door Handles Left</TD><TD class=xl66 style="BORDER-RIGHT: #3f3f3f 0.5pt solid; BORDER-TOP: #3f3f3f; BORDER-LEFT: #3f3f3f; BORDER-BOTTOM: #3f3f3f 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">19551-01</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">23</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">23</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">$ 26.00 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$598.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #3f3f3f 0.5pt solid; BORDER-TOP: #3f3f3f; BORDER-LEFT: #3f3f3f 0.5pt solid; BORDER-BOTTOM: #3f3f3f 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #f2f2f2" height=20>Barn Door Handle Left</TD><TD class=xl66 style="BORDER-RIGHT: #3f3f3f 0.5pt solid; BORDER-TOP: #3f3f3f; BORDER-LEFT: #3f3f3f; BORDER-BOTTOM: #3f3f3f 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">4C3Z78431A03AB</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ford</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">4</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">4</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">$ 51.76 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$207.04</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #3f3f3f 0.5pt solid; BORDER-TOP: #3f3f3f; BORDER-LEFT: #3f3f3f 0.5pt solid; BORDER-BOTTOM: #3f3f3f 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #f2f2f2" height=20>Barn Door Handle Right</TD><TD class=xl66 style="BORDER-RIGHT: #3f3f3f 0.5pt solid; BORDER-TOP: #3f3f3f; BORDER-LEFT: #3f3f3f; BORDER-BOTTOM: #3f3f3f 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">YC3Z7843400AB</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ford</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">7</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">7</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">$ 36.07 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$252.49</TD></TR></TBODY></TABLE>

 

[RSimmons]

Let me be sure I understand - you are trying to keep a running log of all changes over time, not just a list of the most current changes, right?

If that's the case, I am not sure you can do that using conditional formatting. (I am no expert, though, so take that with a grain of salt.)

Are you open to using macros? You might be able to use a macro for this; if this is a sheet other people use I would start by locking your inventory chart and putting macro buttons to the side of each row that users would have to use to change the rec'd or signed-out columns. (This would ensure the macros were run each time.) The macro could unprotect the sheet, prompt the user to enter the new value in the appropriate cell, then add a string to the running list (wherever you want it) with the current date, the item description, rec'd/checked out, change, total number, etc. Of course, the macro would then need to re-lock the sheet.

If you will be the only person using the sheet, you could probably skip the lock/relock thing, and just have put in buttons that say "add change to log" or something - click on the button whenever you make a change and it will add it to your running log.

There may be a better way to handle it; that's just my first thought. Good luck!

 

[BrickCityDevil]

I am open to using whatever works!

 

[BrickCityDevil]

How can I use a Macro to help me?

 

[RSimmons]

Do you have any experience with using macros? My experience is pretty basic, and self-taught so I am probably full of bad habits. But you can write a macro, and attach it to a button like I described above. I had only partly thought out the idea...it would work best if you have a semi-set list of parts themselves (in other words, you aren't always adding new types of inventory).

For instance, you could write a macro called RecRInsideDH, and link it to a button that says "Right Inside Door Handles Received." The macro would need to do something like this:

-unlock the spreadsheet
-prompt the user for the quantity of right inside door handles received (and store this value as a variable)
-add new entered value to the amount already in the cell, and return the total to the cell (so before you had received 10, now you received 5 more, the amount now received should be 15... if that is how you want it to go)
-set another variable equal to the total number of right inside door handles now in stock
-go to another sheet where you keep the log of inventory changes
-find the first empty row
-in the first cell of that row, enter the current date; in the next cell, enter "Right Inside Door Handles Received," in the next cell enter the number saved when the user entered it, and in the next cell enter the total door handles you now have (saved as variables, above)
-relock the original spreadsheet

You can also make a similar macro (attached to a similar button) for checked out, and for each other inventory item. Then when someone wants to say an item has been rec'd or checked out, he just clicks on the button and enters the number received or checked out.

Like I said, this may not be the best way to do it, it's just what comes to mind. May be worth a shot, though.

 

[BrickCityDevil]

I dont have experience with Macros and everything that has been explained to me is "Greek". I just want a message to appear in the far right column that states, the current date,item description, the transaction i.e. "Quantity receieved" or "Quantity signed out" and the numeric value resulting from either the Quantity received or quantity signed out.

If conditional formatting is the way I'm open for that, if macro is the way I'm open for that also.

I really need help