Run VBA Macro when cell value updates, but cell value is a formula that references another sheet

[carlsarlsburg]

I am new to using VBA in Excel so please bear with me any help is appreciated.

I am creating an excel sheet that users past in data from another program into sheet 3 (titled 651R-2 Data). Sheet 1 (titled Cover Sheet) references data on sheet using the vlookup formula and displays certain data to be displayed on a coversheet. I came up with the following code that hides certain rows based on a value on the Cover Sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Rows("1:100").EntireRow.Hidden = False
x = Range("D18").Value
Select Case x
Case 0: Rows("40:48").EntireRow.Hidden = True
Case 1: Rows("20:39").EntireRow.Hidden = True
End Select

End Sub

In Cell D18 the following formula is written, =VLOOKUP(C18,'651R-2 Data'!D$1:E$582,2,FALSE). On sheet 651R-2 Data, the value that it looks up only ranges from 0 to 1.

With Worksheet_SelectionChange whenever I click on another cell in Excel, the hidden rows flicker on and off, which is undesirable. It seems that if I change this to Worksheet_Change, changing the value that D18 looks up on sheet 651R-2 Data will not run the VBA macro to update which rows should be hidden rows (maybe this is because cell D18 is a referencing another sheet so the formula isn't changing so Worksheet_Change doesn't see this as a change even if it evaluates to a different value).

What can I do to update the hidden rows on the Cover sheet with my VBA macro, when the user pastes data into sheet 651R-2 that changes what cell D18 evaluates to without using Worksheet_SelectionChange which causes the rows to flicker?

Thank you!

 

[Joe4]

See if this makes any difference:

Private Sub Worksheet_Calculate()

    Application.EnableEvents = False

    Rows("1:100").EntireRow.Hidden = False

    Select Case x
        Case 0: Rows("40:48").EntireRow.Hidden = True
        Case 1: Rows("20:39").EntireRow.Hidden = True
    End Select
    
    Application.EnableEvents = True
    
End Sub

Other than that, without having access to your sheets, I really cannot day what might be interfering with it (since I cannot reproduce the error on my side).

 

[carlsarlsburg]

Joe4, this fixed it, Thank you so much!

 

[Joe4]

You are welcome.
Glad that did the trick for you!