Macro to update cell when a condition is met?

[iAmPatch]

Hi,

Would there be an easy to read and understand VBA code that updates a certain cell once a condition has been met? I'm currently trying to build a dynamic Inventory Sheet but got stuck at this part.

Right now I have a column wherein it's labeled as "Vendor Balance". It identifies how many more items are pending from our supplier. Once it reaches "0"; we would like the cell to be automatically updated to "Complete". Is this possible?

Thank you

 

[My Aswer Is This]

So are you saying if the value in column G is 0 then cell value should now say complete.
How does this value change? Is it the result of a formula or manually entered.

 

[iAmPatch]

Hi @My Aswer Is This

Yes to the first sentence. If column G is 0 then it should automatically be updated "Complete".

Column G is computed via formula. Column E (# of items ordered) - Column F (delivered). So if Column G is like 10; the cell shouldn't be updated at all.

 

[My Aswer Is This]

So if you have a formula in column G row 4 and column G row 4 now has 0 and we insert "Completed" in column G row 4
We will overwrite the formulas.

So how do we solve that problem?

 

[My Aswer Is This]

If you want to use Macros to do this then.
We do not need formulas.

We would tell a script when a value in a certain cell changes then some action happens.

So if you enter a value like 1 in column H
then the value in column G would be reduced by 1 and if the value in column G reached 0 the value would change to completed

This could all happen automatically when you enter a value in a certain cell

Would this work.

So if this would work tell me in what column would you be entering values in that would change the value in column G

 

[iAmPatch]

Hi @My Aswer Is This

Ideally the goal is to override the formula in Column G Row 4 if it already appears as 0. Then if Column G Row 5 is 10; it should remain as 10.

I did try to do the basic formula of =IF on Column H; wherein, =IF(G4=0,"Completed",G4); then copied it towards the end where there is data. But we want to eliminate this step / column and just have the formula replace Column G Rows (where value is 0).

Though I'm really stuck

Is this not possible?

 

[My Aswer Is This]

So if column E equals 10 and column F equals 10 then column G equals Complete is that true?

If not column G equals what?

Column E minus column F is that correct.

Are columns F and E entered manually?

 

[My Aswer Is This]

Try this:

Any time you change a value into column E or F the script will run
And the value in column G will change
See if this works. You will need no formulas in column G

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 4-4-18 4:45 AM EDT
If Not Intersect(Target, Range("E:F")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
'Dim ans As Long
ans = Target.Value
r = Target.Row
Cells(r, "G").Value = Cells(r, "F").Value - Cells(r, "E").Value
If Cells(r, "G").Value = 0 Then Cells(r, "G").Value = "Completed"
End If
Exit Sub
M:
MsgBox "You entered  " & ans & "  into cell which is a improper value"
End Sub

 

[My Aswer Is This]

I made a mistake:

Try this.
This script starts working in row(2)
See previous instructions in previous posting:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 4-4-18 4:55 AM EDT
If Not Intersect(Target, Range("E:F")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row > 1 Then
Dim r As Long
'Dim ans As Long
ans = Target.Value
r = Target.Row
Cells(r, "G").Value = Cells(r, "E").Value - Cells(r, "F").Value
If Cells(r, "G").Value <= 0 Then Cells(r, "G").Value = "Completed"
End If
End If
Exit Sub
M:
MsgBox "You entered  " & ans & "  into cell which is a improper value"
End Sub

 

[iAmPatch]

I made a mistake:

Try this.
This script starts working in row(2)
See previous instructions in previous posting:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 4-4-18 4:55 AM EDT
If Not Intersect(Target, Range("E:F")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row > 1 Then
Dim r As Long
'Dim ans As Long
ans = Target.Value
r = Target.Row
Cells(r, "G").Value = Cells(r, "E").Value - Cells(r, "F").Value
If Cells(r, "G").Value <= 0 Then Cells(r, "G").Value = "Completed"
End If
End If
Exit Sub
M:
MsgBox "You entered  " & ans & "  into cell which is a improper value"
End Sub

You are AWESOME!!! thank you so much :D
I did make some changes with the cell references as needed and it still works

Last question though; I'm often seeing the "If Not Intersect (Target....." statement. May I kindly ask what this means in English language?