Auto delete in Excel

Question

Is it possible to accomplish the following scenario?

1. G2 has an "X" in the cell
2. Once my imported data causes H2 to have a higher value than J2, I would like for the "X" in G2 to be automatically deleted. Obviously, I have 100's of rows that need to be able to do that, not just row 2.

What I have is this:

When my "available qty" falls below the MIN, it turns the cells red. When I order those, I put an x in the Ordered column and it turns it back to green so I don't have to keep looking at it in the red. I filter by red so I know what I need to order. I just want an automatic way for the x to be deleted once the available qty is more than the min.

Thanks.

Answer 1

I see where you're going with that but it's not quite what I need.

I have to manually put the x's in when I've ordered parts. I want the x's to automatically delete once H is higher than J.

Hi,

You need vb code for that. Right click the worksheet tab where you want this, view code and paste the code in on the right.

The code will execute for any change on the worksheet but that should not give a problem

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastRow As Long, c As Range

LastRow = Cells(Rows.Count, "H").End(xlUp).Row

Application.EnableEvents = False

For Each c In Range("H2:H" & LastRow)

    If c.Value > c.Offset(, 2) Then

        c.Offset(, -1).ClearContents

    End If

Next

Application.EnableEvents = True

End Sub

Answer 2

This is actually causing my spreadsheet to lock up occasionally.

Can this be accomplished with a manually ran macro instead?

Answer 3

I think that may work.

Thank you.

Answer 4

I see where you're going with that but it's not quite what I need.

I have to manually put the x's in when I've ordered parts. I want the x's to automatically delete once H is higher than J.

Answer 5

Hi,

In cell G2, enter this formula and copy down

=IF(H2<=I2,"X","")

Hope this helps.