How to trigger worksheet change event if some values are link from another workbook

[Summer7sun]

Hello Friends
I have 2 workbooks named as N1 and M1 .. M1 workbook just has some data in A9 to A24 thats all no code nothing, Now I copied the Data and pasted it as a link on N1 workbook in A column I have a formula in Column D9:D24 which returns "Yes" or "No" when a criteria is met. I have a macro which copies and pastes data to column E and D
if Column D contains "Yes" or "No"

Sub FindIt()


Sheets("Rice").Select
Application.ScreenUpdating = False
Dim rng As Range
For Each rng In Range("D9:D24")
Select Case rng.Value
Case "Yes"
If Cells(rng.Row, 5) = "" Then
Cells(rng.Row, 5).Value = Cells(rng.Row, 1).Value
End If
Case "No"
If Cells(rng.Row, 6) = "" Then
Cells(rng.Row, 6).Value = Cells(rng.Row, 1).Value
End If
End Select
Next rng
Application.ScreenUpdating = True

End Sub

and I have a Worksheet Change event


Private Sub Worksheet_Change(ByVal Target As Range)
Dim WkRg As Range
Set WkRg = Range("D9:D24")
If Not (Intersect(Target, WkRg) Is Nothing) Then Exit Sub
Call FindIt
End Sub


It works fine on the present sheet if the value is changed in column A to trigger Yes or No if typed in Manually "Note: A is a copy paste link of A from workbook M1" And Column A updates every sec.
So in this scenario if the values are changed in M1 workbook to trigger yes or no in N1 workbook wanted the macro to be triggered if column D has Yes or No

My problem is it wont trigger the macro if the Values are changed in M1 Workbook

 

[Leith Ross]

Hello Summer7sun,

Results arising from formula calculations do not trigger a Worksheet Change event.

 

[Summer7sun]

Hello Leith ... I dont know much about code ... I have this code and wanted it to trigger my macro... how do I go about it