code works with manual input but not with a formula

[alan myers]

on the hide show sheet (b2), when I just input y or n, it works
but with this formula, it does not can someone tell me why
formula is =IF(VLOOKUP($E2,'Carrier Info'!$B$3:$D$197,2,FALSE)="primary","n","y")

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range
Dim fr As Long
Dim lr As Long

' See if cell in cell B manually updated
Set rng = Intersect(Target, Range("B:B"))
If rng Is Nothing Then Exit Sub

' Loop through updated cells in column B
For Each cell In rng
' Capture rows to update
fr = Cells(cell.Row, "C").Value
lr = Cells(cell.Row, "D").Value
' Determine to hide or unhide rows
Select Case UCase(cell)
Case "Y"
Sheets("Cover Sheet").Rows(fr & ":" & lr).Hidden = True
Case "N"
Sheets("Cover Sheet").Rows(fr & ":" & lr).Hidden = False
End Select
Next cell

End Sub

my file https://www.mediafire.com/file/2ij5m1degywob6q/Payroll+v6.5+test.xlsm/file

 

[Joe4]

"Worksheet_Change" event procedures ONLY work for data that is manually updated.
It does NOT work against data that is changed by formulas.

There is a "Worksheet_Calculation" event procedure which runs anytime any cell on the sheet is re-calculated.
So that would be triggered in any cell's value changes due to a formula.
However, there is a BIG caveat with this. There is no "Target" range in a "Worksheet_Calculation".
That is because Excel cannot tell which cell's value changed, only that there was a recalculation somewhere on the sheet.
So you cannot isolate the value that changed if it is a formula.

If you don't mind having the code run through your entire range every time a calculation happens, then you can use this "Worksheet_Calculation" event.
It might slow it down a bit, depending on the size of your data, but it should work.

 

[alan myers]

how do i do this

 

[Joe4]

It would look something like this:

Private Sub Worksheet_Calculate()

    Dim rng As Range
    Dim cell As Range
    Dim fr As Long
    Dim lr As Long

'   Set range equal to all of column B down to last row of data
    Set rng = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)

'   Loop through all cells in column B
    For Each cell In rng
'       Capture rows to update
        fr = Cells(cell.Row, "C").Value
        lr = Cells(cell.Row, "D").Value
'       Determine to hide or unhide rows
        Select Case UCase(cell)
            Case "Y"
                Sheets("Cover Sheet").Rows(fr & ":" & lr).Hidden = True
            Case "N"
                Sheets("Cover Sheet").Rows(fr & ":" & lr).Hidden = False
        End Select
    Next cell

End Sub

So, any time any calculation happens on the sheet, it will loop through all of column B and process the code for each and every row of column B.

 

[alan myers]

come up with an error fr = Cells(cell.Row, "C").Value

 

[Joe4]

come up with an error fr = Cells(cell.Row, "C").Value

OK, I did not change that line of code (that came right from your code).

I am guessing that there may be header or trailer rows of data that we need to ignore, because columns C and D in those rows may contain text instead of numbers, blowing it up.
So you are going to need to tell us more about your data. What row in column B do we want to start searching on?
And do we go all the way to the end, or are there other rows we will need to exclude because columns C and D do not contain numbers.

If it is just row 1 that needs to be excluded, then just change this line:

    Set rng = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)

to this:

    Set rng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

 

[alan myers]

start on row 2 all the way to the end

 

[Joe4]

OK, then the update I mentioned at the end of my previous post should work, i.e.

Private Sub Worksheet_Calculate()

    Dim rng As Range
    Dim cell As Range
    Dim fr As Long
    Dim lr As Long

'   Set range equal to column B starting on row 2 down to last row of data
    Set rng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

'   Loop through all cells in column B
    For Each cell In rng
'       Capture rows to update
        fr = Cells(cell.Row, "C").Value
        lr = Cells(cell.Row, "D").Value
'       Determine to hide or unhide rows
        Select Case UCase(cell)
            Case "Y"
                Sheets("Cover Sheet").Rows(fr & ":" & lr).Hidden = True
            Case "N"
                Sheets("Cover Sheet").Rows(fr & ":" & lr).Hidden = False
        End Select
    Next cell

End Sub

 

[alan myers]

works fine. Thank you for the help greatly appreciated

 

[Joe4]

You are welcome!

Note: That I re-marked the solution as the one that contains the complete code, as that makes more sense if people were to look at just the original question and the solution.