How to Run a Macro if the cell value changes

Question

I created a macro that filters based on a cell value which works fine.

Range("A1:L1").AutoFilter Field:=4, Criteria1:=Range("U1")

I need this macro to run everytime the cell value changes. I wrote a macro but it is not working i dont get any errors just nothing happens.

I tried:

Private Sub Worksheet_Tabelle1(ByVal Target As Range)

If Target.Address = "$U$1" Then

Application.EnableEvents = False

Range("A1:L1").AutoFilter Field:=4, Criteria1:=Range("U1")

Application.EnableEvents = True

End If

End Sub

This version should just execute the code and not call a macro. I changed the Worksheet_xxxxx to the sheet name and tried other things.

I also tried:

Private Sub Worksheet_Arbeitstabelle(ByVal Target As Range)

If Target.Address = "$U$1" Then

Call Macro1

End If
End Sub

This version should call the following Macro:

Sub Macro1()


Range("A1:L1").AutoFilter Field:=4, Criteria1:=Range("U1")

End Sub

I put all the Private Sub macros on the Worksheet and the Macro1 in a modul. The file is .xlsm and doesnt have any problem running other macros so i dont know why its not working. My guess is i probably did something wrong with the names so here are the names:

@FunThomas I mentioned in the Question where i put the Code its not in a modul — Cedric
@SiddharthRout Thanks i read the answer and changed the Worksheet_Tabelle to Worksheet_change now its working thanks alot — Cedric

Adam Thompson Adam Thompson · Accepted Answer · 2019-04-08T08:32:54

Try this:

    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been 
        ' changed.
        ' Place your code here.
        MsgBox "Cell " & Target.Address & " has changed."

    End If
End Sub

How to Run a Macro if the cell value changes

2 Answers