T
timmtamm
I need a alteration to my program that will hide various rows and change
print area based on a separate cell than the current one. For example, if I
type .84 into B20 I want it to adjust things as written below, but if I then
type in JP into cell J20 I want it to hide past row 94 and print before that
row. I tried doing this by setting up an all new sub for the worksheet in
addition to this one (using this one as a pattern), but excel didn't like
that.
Sub Worksheet_Change(ByVal Target As Range)
Dim v As Double
Set t = Target
Set r = Range("B20")
If Intersect(t, r) Is Nothing Then
Exit Sub
Else
v = Val(r.Value)
End If
Select Case v
Case 0.9, 0.94, 0.76, 0.75, 0.78
Rows("63:93").EntireRow.Hidden = False
Rows("94:124").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:I$93"
Case 0.84
Rows("63:124").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$62"
Case Is < 0.75, Is > 0.94, Is > 0.79 < 0.84
Rows("63:124").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$124"
Case Is > 0.84 < 0.9, Is > 0.9 < 0.94
Rows("63:124").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$124"
End Select
End Sub
print area based on a separate cell than the current one. For example, if I
type .84 into B20 I want it to adjust things as written below, but if I then
type in JP into cell J20 I want it to hide past row 94 and print before that
row. I tried doing this by setting up an all new sub for the worksheet in
addition to this one (using this one as a pattern), but excel didn't like
that.
Sub Worksheet_Change(ByVal Target As Range)
Dim v As Double
Set t = Target
Set r = Range("B20")
If Intersect(t, r) Is Nothing Then
Exit Sub
Else
v = Val(r.Value)
End If
Select Case v
Case 0.9, 0.94, 0.76, 0.75, 0.78
Rows("63:93").EntireRow.Hidden = False
Rows("94:124").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:I$93"
Case 0.84
Rows("63:124").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$62"
Case Is < 0.75, Is > 0.94, Is > 0.79 < 0.84
Rows("63:124").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$124"
Case Is > 0.84 < 0.9, Is > 0.9 < 0.94
Rows("63:124").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$124"
End Select
End Sub