Hello,
I'm new to VBA and have a problem, so I hope - you can help me 
If I have two worksheets, first called DATA and second called FORM, and I want to hide rows in FORM based on the cell value (drop down list with 7 options, which should hide different ranges) in DATA. I wrote the code in FORM sheet and I made in cell B1 easy function like B1=DATA!A1 - and after changing this value in DATA the value changes, but nothing is automatically hidden. I can hide if after I enter the cell B1, press the formula on formula bar and click enter - then it works.
My code is written in FORM sheet and looks like this:
Code
- Private Sub Worksheet_Change(ByVal Target As Range)
- ActiveSheet.Activate
- If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then
- Select Case Target.Value
- Case Is = "Not Applicable": Rows("12:18").EntireRow.Hidden = True
- Range("A1:A11").EntireRow.Hidden = False
- Case Is = "1": Rows("14:18").EntireRow.Hidden = True
- Range("A1:A13").EntireRow.Hidden = False
- Case Is = "2": Rows("15:18").EntireRow.Hidden = True
- Range("A1:A14").EntireRow.Hidden = False
- Case Is = "3": Rows("16:18").EntireRow.Hidden = True
- Range("A1:A15").EntireRow.Hidden = False
- Case Is = "4": Rows("17:18").EntireRow.Hidden = True
- Range("A1:A16").EntireRow.Hidden = False
- Case Is = "5": Rows("18:18").EntireRow.Hidden = True
- Range("A1:A17").EntireRow.Hidden = False
- Case Is = "6": Rows("18").EntireRow.Hidden = True
- Range("A1:A18").EntireRow.Hidden = False
- End Select
- End If
- End Sub
Could you tell me how to change it to make it automatic? I mean when B1 change value, my rows are hidden or unhidden.