If cell contains string, clear values from cells in the same row

[ldavis89]

Hi,

I'm using a table in Excel to track product inventory, and I'd like to build in what seems like a simple function. I have a column for "Stock Level", which will say "Low" if the current stock falls below a preset minimum (or "Out of Stock"), which changes back to "Good" once enough stock is added to our inventory. I have another column, "Status", for tracking the status of items we need to order ("RFQ to vendor . . ."), allowing everyone to see what the status is, when to expect more, etc.

I'd like to automatically clear the "Status" column when the "Stock Level" column changes to "Good".

I am a total rookie with VBA, so I've tried running a loop that iterates through each row, and looks for Good in a given column (with the InStr fxn) but no luck!

Any help is much appreciated.

Thanks!

[PCI]

A sheet event macro could certainly do the job but to prepare it correctly we need an Excel sample.
Can you attach a short one.

[ldavis89]

Here's a sample, 1st sheet is the inventory list, second sheet is where we remove from stock (scan barcode, enter quantity), and similar process for the add to stock sheet. The first sheet is where the macro would be used.

In case it matters, the macros on the remove and add to stock sheets separate a comma delimited list, and auto selects the next empty cell for scanning.

Thanks

[PCI]

In sheet Inventory List' s code you could put next statement

Option Explicit
Private Sub Worksheet_Activate()
Const SLCol = "H"
Const StCol = "I"
Dim I As Integer
    Application.ScreenUpdating = False
    For I = 2 To Cells(Rows.Count, StCol).End(3).Row
        If (Cells(I, SLCol) = "GOOD") Then Cells(I, SLCol) = ""
    Next I
    Application.ScreenUpdating = True
End Sub