I have written the code below that hides rows based on a data validation cell. The code works fine when the Target.Value cell refers to the text in the desired cell i.e. If Target.Value = "Net to Gross" then the code works as desired.

However, the model does not work if I change the target.value to refer to a range in another worksheet. My range refers to only 1 cell (NB: the target.address cells are data validated cells that refer to ranges for the options, but the target.value cells are referring to ranges that target only 1 cell)

Can someone please help in solving this problem. As you can see below, I have added "with sheets under private sub worksheet" to refer to the location of the ranges in the other sheet but that does not solve the problem when I change the target.value.

I have also added the quotation marks in the code below where the problem arises.

Help would be greatly appreciated.

Many thanks.

 
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Quad")

If Intersect(Target, Range("E25,e33,e53")) Is Nothing Then Exit Sub

If Target.Address = "$E$25" Then
If Target.Value = "" Then
Range(Rows(27), Rows(62)).EntireRow.Hidden = True
Range("e25").Select
End If
If Target.Value = "Net to Gross" Then 'need this to refer to a range cell in another worksheet where range("rNettoGross")'
Range(Rows(27), Rows(35)).EntireRow.Hidden = False
Range(Rows(36), Rows(37)).EntireRow.Hidden = True
Range(Rows(38), Rows(44)).EntireRow.Hidden = False
Range(Rows(45), Rows(62)).EntireRow.Hidden = True
Range("e25").Select
End If
If Target.Value = "Gross to Net" Then 'same as above need to refer to a range cell in another worksheet where range("rGrosstoNet")'
Range(Rows(27), Rows(44)).EntireRow.Hidden = True
Range(Rows(45), Rows(55)).EntireRow.Hidden = False
Range(Rows(56), Rows(57)).EntireRow.Hidden = True
Range(Rows(58), Rows(62)).EntireRow.Hidden = False
Range("e25").Select
End If
End If

If Target.Address = "$E$33" Then
If Target.Value = "Fixed" Then 'should refer to range(rMFixed)'
Range(Rows(34), Rows(35)).EntireRow.Hidden = False
Range(Rows(36), Rows(37)).EntireRow.Hidden = True
Range("e33").Select
End If
If Target.Value = "Percentage" Then 'refer to range(rMPercentage)'
Range(Rows(34), Rows(35)).EntireRow.Hidden = True
Range(Rows(36), Rows(37)).EntireRow.Hidden = False
Range("e33").Select
End If
End If

If Target.Address = "$E$53" Then
If Target.Value = "Fixed" Then 'refer to range(rMFixed)'
Range(Rows(54), Rows(55)).EntireRow.Hidden = False
Range(Rows(56), Rows(57)).EntireRow.Hidden = True
Range("e53").Select
End If
If Target.Value = "Percentage" Then 'refer to range(rMPercentage)'
Range(Rows(54), Rows(55)).EntireRow.Hidden = True
Range(Rows(56), Rows(57)).EntireRow.Hidden = False
Range("e53").Select
End If
End If

Application.ScreenUpdating = False

End With

End Sub