Excel for inventory management

[btkartik]

Hello all,

I am new to the world of Excel possibilities. I have a situation. I need to create an excel with about 100 columns, and 2 /3 rows. Column names are "Product Name", "Total Stock", and "Safety Stock". The inventory manager updates the excel each time a product goes out of the warehouse.

Now, can I make sure that when the "Total Stock" value reaches the "Safety Stock" value, there is a popup message - "Safety stock reached, Order immediately"? It'd be great if the cell color can turn red.

Please help me out with this.
Thanks in advance.

Cheers!

 

[mumps]

It's difficult to help with the amount of information you have supplied. You mention that you have about 100 columns but only name three of them: "Product Name", "Total Stock", and "Safety Stock". Did you mean 100 rows and 3 columns? I think that it would be much easier to follow if I could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific rows and columns.

 

[btkartik]

Oops! Sorry, it is 100 rows and 3 columns.

It's difficult to help with the amount of information you have supplied. You mention that you have about 100 columns but only name three of them: "Product Name", "Total Stock", and "Safety Stock". Did you mean 100 rows and 3 columns? I think that it would be much easier to follow if I could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific rows and columns.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Update the stock in column B ("Total Stock") and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    If Target >= Target.Offset(0, 1) Then
        MsgBox ("Safety stock for " & Target.Offset(0, -1) & " has been reached. Order immediately.")
        Target.Offset(0, -1).Interior.ColorIndex = 3
    End If
End Sub

 

[btkartik]

Thanks a ton for this!! Works well

Cheers!

 

[btkartik]

Thanks a ton for this!! Works well

Cheers!

Hi again,

I have used the following code to get a popup message when the value is > 100, and also change the cell color to red. But, what do I do if i want the popup message and color change when the column B value is <= to the column C value?

Thanks in advance.

Cheers!

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 Then
ThisRow = Target.Row
If Target.Value > 100 Then
Range("B" & ThisRow).Interior.ColorIndex = 3
MsgBox ("Safety stock for " & Target.Offset(0, -1) & " has been reached. Order immediately.")
Else
Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub

 

[mumps]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    If Target <= Target.Offset(0, 1) Then
        MsgBox ("Safety stock for " & Target.Offset(0, -1) & " has been reached. Order immediately.")
        Target.Interior.ColorIndex = 3
    Else
        Target.Interior.ColorIndex = xlNone
    End If
End Sub