Run VBA when formula changes target cell to hide rows

[neilcsmith1984]

Hi,

I have a problem running vba from a target cell that derives from a formula.

I have created the "example book" which shows what I am trying to achieve.

I have 2 dependent combo boxes that update a forecast table on sheet 2. The forecast table will vary in size depending on the combo box selections, eg, one selection may only have 1 row of data whereas another could have 4.

I have the below vba code that hides the rows that are not used in the forecast table based on the value in A1, which derives from a formula, however, when I change the combo boxes the vba doesn't update. It will only update when i actually change the value of A1.

I have tried to use calculate events etc, but I am very new to VBA and I am struggling to find a solution that will run the vba code when the formula updates the value in A1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Rows("8:13").Hidden = False
If Target.Value > 0 And Target.Value < 19 Then
Rows(Target.Value + 8 & ":13").Hidden = True
End If
End If
End Sub

EDIT:
I do not know how to upload my excel sheet

 

[Joe4]

You cannot upload files, but you can post screen images using the tool mentioned here: XL2BB - Excel Range to BBCode

What exactly is your formula in cell A1?

 

[neilcsmith1984]

thanks for the response
the formula in cell A1 is: =COUNTIFS(FC[PROD],B5,FC[MARKET],BD1)

 

[Joe4]

See if this does what you want:

Private Sub Worksheet_Calculate()
    Rows("8:13").Hidden = False
    If Range("A1") > 0 And Range("A1") < 19 Then
        Rows(Range("A1").Value + 8 & ":13").Hidden = True
    End If
End Sub