Call a macro if certain cell >1

Question

I would like to run the following macro automatically if cell e24 >1.

It works fine as a macro, but would like it to run automatically.

Can this be edited to make it automatic?

Thanks for your help

Sub DUPLICATES()
If Range("e24") > 1 Then
Cells(Rows.Count, "d").End(xlUp).Select
ActiveCell.ClearContents
ActiveSheet.Range("E24").Select

End If

End Sub

Answer 1

Right-click on the sheet tab
Choose "View Code"
Paste in the code below
Close the VBA editor

Write a value into E24

Andreas.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address(0, 0)
Case "E24"
DUPLICATES
End Select
End Sub

Answer 2

This did not work and gave me this error:

There is another "Private Sub Worksheet_Change(ByVal Target As Range)" vba in this area.

This also conflicts with a few buttons for macros in the same spreadsheet.

I just added your code below the other one which has nothing to do with this issue.

I thought there might be a way to make this work without upsetting the rest.

Thanks

Answer 3

Hi there

The following code goes into the worksheet change event panel, as shown in the picture below.

The code will automatically execute when you enter a value greater than 1 in cell E24

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("E24")) Is Nothing Then

If Target.Value > 1 Then 

    Cells(Rows.Count, "D").End(xlUp).ClearContents 

End If 

Target.Select

End If

End Sub

I hope this helps you

Regards

Jeovany

Answer 4

Hi,

When I double click on sheet 1 there is vba there already listed under (General) (Declarations).

I tried to add your code there but didn't work.

Can get Worksheet and Change by clicking on General and Declarations to change it to Worksheet and Change, but concerned that this will effect the vba that already exists.

Do I need a new worksheet event panel and, if so, how to?

Obviously I am pretty remedial at this, but try !

Thanks for your patience.

Answer 5

Please, Select "Worksheet" and then copy/paste the code