VBA code doesn't run when cell is changed by a formula

To capture the changes by a formula you have to use the Worksheet_Calculate() event. To understand how it works, let's take an example.

1. Create a New Workbook.
2. In Sheet1 Cell A1, put this formula =Sheet2!A1+1

Now In a module paste this code

Public PrevVal As Variant

Paste this in the Sheet Code area

Private Sub Worksheet_Calculate()    If Range("A1").Value <> PrevVal Then        MsgBox "Value Changed"        PrevVal = Range("A1").Value    End IfEnd Sub

And lastly in the ThisWorkbook Code area paste this code

Private Sub Workbook_Open()    PrevVal = Sheet1.Range("A1").ValueEnd Sub

Close and Save the workbook and reopen it. Now Make any change to the cell A1 of Sheet2. You will notice that you will get the message box MsgBox "Value Changed"

SNAPSHOTS

The worksheet_change event will only fire on manual user changes. I think your best bet would be to implement this as a worksheet change event on your Worksheet B, where I presume the user input changes are taking place.

There are some alternatives which I'll suggest if this really doesn't suit you, but I think this is probably by far the best option.

Edit: Another suggestion per following comments

The ThisWorkbook object has an event SheetChange, which will be fired any time any sheets in the workbook are changed. If you can identify the ranges where data will be entered on each of the B sheets you can then use these ranges as in your original code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    If Not Sh Is Sheets("Worksheet A") Then        If Intersect(Sh.Range("B1:B5"), Target) Then            'Call MailAlert as required here        ElseIf Intersect(Sh.Range("B10:B20"), Target) Then            'Call MailAlert as required here        Else ' Etc...            'Call MailAlert as required here        End If    End IfEnd Sub

Let me know how that goes.