alan myers
Board Regular
- Joined
- Oct 31, 2017
- Messages
- 119
- Office Version
- 365
- Platform
- Windows
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
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