VBA Code will not update as target cell is updated using a formula

[neilcsmith]

Hi All,

I am new to VBA and know virtually nothing. I have the following code that hides rows based on the cell value in A99. It works fine, however, when the value in A99 changes, the vba code does not run unless i click on cell A99.

Is there a way to adapt the code to update anytime the formula in cell A99 changes the value.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$99" Then
Rows("102:119").Hidden = False
If Target.Value > 0 And Target.Value < 19 Then
Rows(Target.Value + 101 & ":119").Hidden = True
End If
End If
End Sub

Thanks in advance.

NS

[Paul_Hossler]

Seems to work for me

What specifically causes it to fail?

Is there an Application.EnableEvents = False somewhere in the macros?

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$99" Then
        Rows("102:119").Hidden = False
        
        If Target.Value > 0 And Target.Value < 19 Then
            Rows(Target.Value + 101 & ":119").Hidden = True
        End If
    End If
End Sub

[neilcsmith]

Seems to work for me

What specifically causes it to fail?

Is there an Application.EnableEvents = False somewhere in the macros?

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$99" Then
        Rows("102:119").Hidden = False
        
        If Target.Value > 0 And Target.Value < 19 Then
            Rows(Target.Value + 101 & ":119").Hidden = True
        End If
    End If
End Sub

Sorry, I forgot to mention... the value in cell A99 is a formula. When the formula updates the cell value, the vba does not run.

Do you know how to adjust the formula to update anytime A99 changes

Thanks

[Paul_Hossler]

Try Worksheet_Calculate instead