Repeat VBA Function for Separate Events in Same Worksheet

[mlp1415]

Hi all,

I am rather new to Visual Basic, but I am trying to duplicate a function that I have wherein if an amount in cell N165 is greater than 5000, then the two rows below the cell (in this case, rows 166 and 167) will become visible. I am trying to do this in multiple separate instances on the same worksheet (with an amount greater than 5000 in N168 revealing 169 and 170, N171 revealing 172 and 173, etc.) Is there a way to accomplish this? Any help would be greatly appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Address = "$N$165" Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
If Target > 5000 Then
Range("166:167").EntireRow.Hidden = False
Else
Range("166:167").EntireRow.Hidden = True
End If
If Not Target.Address = "$N$168" Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
If Target > 5000 Then
Range("169:170").EntireRow.Hidden = False
Else
Range("169:170").EntireRow.Hidden = True
End If
If Not Target.Address = "$N$171" Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
If Target > 5000 Then
Range("172:173").EntireRow.Hidden = False
Else
Range("172:173").EntireRow.Hidden = True
EndIf



End Sub

 

[JLGWhiz]

Not sure if this is what you want, but give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ary As Variant
If Not Intersect(Target, Range("N165, N168, N169")) Is Nothing Then
    ary = Array(Range("N165"), Range("N168"), Range("N171"))
    For i = LBound(ary) To UBound(ary)
        If ary(i).Value > 5000 Then
            ary(i).Offset(1, 0).Resize(2, 1).EntireRow.Hidden = False
        Else
            ary(i).Offset(1, 0).Resize(2, 1).EntireRow.Hidden = True
        End If
    Next
End If
End Sub