Clear Cell Contents if sum of two cells exceeds

[hrdpgajjar]

Hi there,
I have four total cells in excel

1. Quantity(C1)
2. HU amount(A1)
3. FU Amount(B1)
4. Allowed amount(z1)



Now what i want to do is,

If I enter value If I enter value 100 in C1 and by this if the total of A1 + B1 =Z1 exceeds some value (for example 1000) then cell C1 clears it contents.

How can i do this in excel vba


Thanks

 

[Joe4]

So, let me see if I understand this correctly.
When someone enters a value in cell C1, if the value in cell Z1 exceeds 1000, then cell C1 should be cleared?
Is that correct?

If so, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VB code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if cell C1 was not updated
    If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
    
'   Check the value of cell Z1
    If Range("Z1") > 1000 Then
'       Clear cell C1 if over 1000
        Application.EnableEvents = False
        Range("C1").ClearContents
        Application.EnableEvents = True
    End If

End Sub

This code will run automatically as cell C1 is manually updated.

 

[hrdpgajjar]

thanks it works

So, let me see if I understand this correctly.
When someone enters a value in cell C1, if the value in cell Z1 exceeds 1000, then cell C1 should be cleared?
Is that correct?

If so, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VB code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if cell C1 was not updated
    If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
   
'   Check the value of cell Z1
    If Range("Z1") > 1000 Then
'       Clear cell C1 if over 1000
        Application.EnableEvents = False
        Range("C1").ClearContents
        Application.EnableEvents = True
    End If

End Sub

This code will run automatically as cell C1 is manually updated.

 

[hrdpgajjar]

So, let me see if I understand this correctly.
When someone enters a value in cell C1, if the value in cell Z1 exceeds 1000, then cell C1 should be cleared?
Is that correct?

If so, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VB code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if cell C1 was not updated
    If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
   
'   Check the value of cell Z1
    If Range("Z1") > 1000 Then
'       Clear cell C1 if over 1000
        Application.EnableEvents = False
        Range("C1").ClearContents
        Application.EnableEvents = True
    End If

End Sub

This code will run automatically as cell C1 is manually updated.

Hi, I have slightly modified the code for my benefits, But for one range its working fine but for other range its not working
Code is as under,

If Intersect(Target, Range("E13:E27")) Is Nothing Then Exit Sub
If Intersect(Target, Range("E32:E51")) Is Nothing Then Exit Sub
If Range("J55") > Range("L55") Then
Application.EnableEvents = False
Range("H13:H27").ClearContents
Range("H32:H51").ClearContents
Application.EnableEvents = True
End If


can u help me ?

 

[Joe4]

The issue is with how you structured your code, especially this part here:

If Intersect(Target, Range("E13:E27")) Is Nothing Then Exit Sub
If Intersect(Target, Range("E32:E51")) Is Nothing Then Exit Sub

Note that if you have an update to cell in the second range, i.e. E40, the first IF will kick them out of the procedure (with the "Exit Sub" part), so it will NEVER satisfy the second if (it can never get there).

That "Exit Sub" logic really only works well with a single logic check. If you have multiple, it is better to go the other way, and check to see if the Target cell is found in your desired range (not if it does not exist in the target range).

So you should write your code something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

'   Check to see if update occurs in first range
    If Not Intersect(Target, Range("E13:E27")) Is Nothing Then
        If Range("J55") > Range("L55") Then
            Range("H13:H27").ClearContents
        End If
    End If
    
'   Check to see if update occurs in second range
    If Not Intersect(Target, Range("E32:E51")) Is Nothing Then
        If Range("J55") > Range("L55") Then
            Range("H32:H51").ClearContents
        End If
    End If

    Application.EnableEvents = True

End Sub

 

[hrdpgajjar]

The issue is with how you structured your code, especially this part here:

If Intersect(Target, Range("E13:E27")) Is Nothing Then Exit Sub
If Intersect(Target, Range("E32:E51")) Is Nothing Then Exit Sub

Note that if you have an update to cell in the second range, i.e. E40, the first IF will kick them out of the procedure (with the "Exit Sub" part), so it will NEVER satisfy the second if (it can never get there).

That "Exit Sub" logic really only works well with a single logic check. If you have multiple, it is better to go the other way, and check to see if the Target cell is found in your desired range (not if it does not exist in the target range).

So you should write your code something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

'   Check to see if update occurs in first range
    If Not Intersect(Target, Range("E13:E27")) Is Nothing Then
        If Range("J55") > Range("L55") Then
            Range("H13:H27").ClearContents
        End If
    End If
   
'   Check to see if update occurs in second range
    If Not Intersect(Target, Range("E32:E51")) Is Nothing Then
        If Range("J55") > Range("L55") Then
            Range("H32:H51").ClearContents
        End If
    End If

    Application.EnableEvents = True

End Sub

thanks it works!!!

 

[Joe4]

You are welcome.

I hope my explanation makes sense!