Workaround for recognizing reference cell changes in Change_Event

Hello,

I have a workbook that utilizes a Worksheet_Change Event to erase relevant cells in the same row. However, it doesn't work since the cells that the Worksheet_Change Event runs on are actually reference cells to another workbook in another department.

I created a macro to "click + enter" every single cell in column B so that the Worksheet_Change Event runs. This works perfectly fine but takes too much time to run for it to be useful (My actual workbook has hundreds of rows). However, if you take a look at the attached file, wouldn't it be possible to create a code that simply finds each cell with a value in column D, and then make it automatically "click + enter" the cell in column B? This seems like a viable solution since there will usually only be 1-5 rows that would need to be activated. This code could then also run on column E & F.

Change Event that I need to run on reference cells in column B:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns("A:A")) Is Nothing Then
    If Target.Value = "" Or Target.Value = 0 Then
        Application.EnableEvents = False
        Target.Offset(0, 1).Resize(1, 3).ClearContents
        Application.EnableEvents = True
    End If
End If

If Not Intersect(Target, Columns("B:D")) Is Nothing Then
    If Range("A" & Target.Row).Value = "" Then
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If
End If
        
End Sub

Current code I use to activate each cell in column B to make Worksheet_Change event run:

Sub Clear_Blanks()

Application.ScreenUpdating = False


    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(Data!R[-5]C[-1]='Input Sheet'!RC[-1],Data!R[-5]C,"""")"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(Data!R[-5]C[-1]='Input Sheet'!RC[-1],Data!R[-5]C,"""")"
    Range("B4").Select

'This code continues until all cells in column B have been activated

Application.ScreenUpdating = True

End Sub

Many thanks for your input!

That definitely would be the ideal answer, but the way these actual workbooks (not my example book) are set up make this solution quite complicated. For example, how does that work if the worksheet that the values are being entered in are from another workbook? Also, based on the primary value being entered in, the value is referenced in 1-5 different workbooks which means that the code will need to look at all dependent workbooks every time it runs.

From my novice perspective, it seems much more straightforward to have a code that simply "click + enters" the cell in column B on Workbook Open if there are values in columns D, E, or F.