Conditional highlighting....

[mjack003]

Hi,

I have a master inventory list with three thousand parts or so on th
first sheet of a workbook. The next thirty or so sheets are thes
parts broken down into their packages. I've created a macro (Thank
dave) so when I ship these parts I can create a packing order b
highlighting all worksheets being shipped and run a macro. That wa
the hard part. How would I go about changing the row color of eac
item on these sheets on the master list? Every item is given a stati
item number which is listed in column A. I imagine there is a way t
do a match function and conditional formatting but with various sheet
selected at the same time this is beyond me. The general idea is th
items already shipped will be highlighted on the master list. Thank
in advance.

Best Regards,
Mjac

 

[David McRitchie]

For Conditional Formatting you have to provide a formula
that produces a True condition or a False condition.
Take a look at my page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Since you do not indicate how you know if an item is shipped,
I'm not going to make a guess as to what you use.

 

[mjack003]

Hi David,

When I ship the items, I highlight the worksheets, which represen
packages, and run my macro off of a custom toolbar. This creates
copy of just these sheets and assigns a PO number in a new workbook.
What I need to know is when I'm copying those sheets if there is an
way to include in my macro a conditional format which will take th
item numbers in each of the selected "packages", which are in column
and highlight those same numbers in column A in a different color o
the master list. That way I can look at my master list and see what
still have in store. Hope this clears it up a bit. Here's my macr
that I run.

Sub macro1()

Dim myStr As String
Dim newWkbk As Workbook

Do
myStr = InputBox(prompt:="Enter Shipping Document Number:")
If Trim(myStr) = "" Then

End If

If IsNumeric(myStr) Then
If Val(myStr) = CLng(myStr) _
And Val(myStr) < 999999 Then
myStr = Format(Val(myStr), "000000")
Exit Do
End If
End If
Loop


ActiveWindow.SelectedSheets.Copy

Set newWkbk = ActiveWorkbook

newWkbk.SaveAs Filename:="J:\Rec_Share\Customers for Shipping\" _
& "PO# " & myStr & " " & Format(Date, "(mm-dd-yy)") & ".xls", _
FileFormat:=xlWorkbookNormal

newWkbk.Close savechanges:=False

End Su

 

[David McRitchie]

Wouldn't you subtract from an inventory count, and perhaps compare
the new level with a level at which you must place an order.

Do I follow that for each purchase order you create a new workbook
and include a worksheet for each item sold. Are these "Material
Data Safety Sheets".