How do I get my Macro to run when Sheet Changes?

[andybason]

Hello,

I've put together the macro below and it runs fine when it's run manually.

I am now trying to get it to run when any change is made to Sheet1 without luck. Can anyone point me in the right direction?

Thank you

Sub m()

Dim ThisCell1 As Range
Dim ThisCell2 As Range
    For Each ThisCell1 In ThisWorkbook.Sheets("Sheet1").Range("A5:A1000")
    'This is the range of cells to check
        For Each ThisCell2 In ThisWorkbook.Sheets("Sheet2").Range("A1:A10")
        'This is the range of cells to compare
            If ThisCell1 <> "" And ThisCell1.Value = ThisCell2.Value Then
                ThisCell1.Offset(, 16).Value = "BF5"
                Exit For
                End If
            Next ThisCell2
        Next ThisCell1

End Sub

 

[Joe4]

In the Sheet module, there is a "Worksheet_Change" event procedure, which is VBA code that is fired whenever a change is manually made to that worksheet.
So we can call your other macro from there.

Place this code in the Sheet1 module (do NOT put it in a Standard/General module, like Module1 - it MUST go in the appropriate sheet module):

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Call m
    Application.EnableEvents = True
End Sub

 

[andybason]

Thanks Joe

 

[Joe4]

You are welcome.