I am trying to hide various rows of a worksheet called "Storage & Distribution" if the value of cell C46 of a worksheet called "General info & study design" is changed. I have set a lookup on the "Storage & Distribution" sheet for reference (C7). The code I am using is shown below but it does not work until I type something into one of the cells of the "Storage & Distribution" sheet (only the red text). Is this something to do with using a Worksheet_Change Vs a Worksheet_Calculate? the rest of the code works fine as this is based on a drop down menu on the"Storage & Distribution" sheet and does not reference any other worksheets or use a formula. Any help would be appreciated.
Code
Private Sub Worksheet_Change(ByVal target As Range)
If Range("k2").Value <> 0 Then
Me.Tab.ColorIndex = 4
Else
Me.Tab.ColorIndex = 3
End If
If Range("c7").Value <= 1.1 Then
Rows("35:138").Hidden = True
Else
Rows("35:138").Hidden = False
End If
If Range("c7").Value <= 2.1 Then
Rows("61:138").Hidden = True
Else
Rows("61:138").Hidden = False
End If
If Range("c7").Value <= 3.1 Then
Rows("87:138").Hidden = True
Else
Rows("87:138").Hidden = False
End If
If Range("c7").Value <= 4.1 Then
Rows("113:138").Hidden = True
Else
Rows("113:138").Hidden = False
End If
If Range("c140").Value <= 1.1 Then
Rows("151:185").Hidden = True
Else
Rows("151:185").Hidden = False
End If
If Range("c140").Value <= 2.1 Then
Rows("160:185").Hidden = True
Else
Rows("160:185").Hidden = False
End If
If Range("c140").Value <= 3.1 Then
Rows("169:185").Hidden = True
Else
Rows("169:185").Hidden = False
End If
If Range("c140").Value <= 4.1 Then
Rows("178:185").Hidden = True
Else
Rows("178:185").Hidden = False
End If
End Sub