Hiding Columns based on value from another worksheet
 

I'm building a pro-forma financial statement that will evaluate an
investment for a given number of years. I need to be able to put in a
number specifying how long I will hold the investment in cell I-6 on a
worksheet called "Data Entry", and then add or subtract columns on a
worksheet called "operating Statement" based on the number from the
data entry worksheet.

The maximum holding period is 10 years, so I figure the easiest way to
do this is to just build my worksheet to have columns for years 0-10,
and then use the macro to hide columns if the holding period is less
than 10. Example, if the holding period is 5, I want the macro to hide
columns 6-10.

How do I do this?

Thanks.

Hiding Columns based on value from another worksheet
 

I've inserted the following code, but there are problems. the code
works fine, unless the value of the target cell is 1. If I set it to
one, then nothing happens when I change the value unless I set it to
10, and then it's like the thing resets and it works fine and adjusts
to whatever number is in the target cell. Why does the value 1 screw
this up? How can I fix it. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Const HOLD_P As String = "I6"


On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address(False, False) = HOLD_P Then
Select Case .Value
Case "1"
Sheets("Operating Statement").Columns("D:L").Hidden
= True
Case "2"
Sheets("Operating Statement").Columns("E:L").Hidden
= True
Case "3"
Sheets("Operating Statement").Columns("F:L").Hidden
= True
Case "4"
Sheets("Operating Statement").Columns("G:L").Hidden
= True
Case "5"
Sheets("Operating Statement").Columns("H:L").Hidden
= True
Case "6"
Sheets("Operating Statement").Columns("I:L").Hidden
= True
Case "7"
Sheets("Operating Statement").Columns("J:L").Hidden
= True
Case "8"
Sheets("Operating Statement").Columns("K:L").Hidden
= True
Case "9"
Sheets("Operating Statement").Columns("L:L").Hidden
= True
Case "10"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
End Select
End If
End With


ws_exit:
Application.EnableEvents = True
End Sub

Hiding Columns based on value from another worksheet
 

I solved it by modifying it as below. i totally rock. please let me
know if there's an easier way to do this. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Const HOLD_P As String = "I6"


On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address(False, False) = HOLD_P Then
Select Case .Value
Case "1"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("D:L").Hidden
= True
Case "2"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("E:L").Hidden
= True
Case "3"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("F:L").Hidden
= True
Case "4"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("G:L").Hidden
= True
Case "5"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("H:L").Hidden
= True
Case "6"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("I:L").Hidden
= True
Case "7"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("J:L").Hidden
= True
Case "8"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("K:L").Hidden
= True
Case "9"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("L:L").Hidden
= True
Case "10"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
End Select
End If
End With

Hiding Columns based on value from another worksheet
 

Using a helper row/range (say row 5) will be better and have two macros - one
to hide and another to unhide. Obviously you will be having your YEAR
headings on a row. Say they are from C4:L4. Name C5:L5 "HICOLS". Name a cell
to identify current year as "CurYr". On C5 enter =IF(C5="CurYR",1,0). Copy
through D5:L5.
You can hide row 5 or format the font colour as white so the numbers become
invisible. Enter the number for the current year in "CurYr" and run the
following macro. For subsequent months in will not be necessary to run the
Unhide macro unless you want to see the previous years numbers.
Note "CurYr" must follow your YEAR headings and can be 2005,2006,2007, etc.
or 1,2,3, whichever you choose.
Sub HideColums()
For Each cell_in_loop In Range("HICOLS")
If cell_in_loop.Value = 0 Then
With cell_in_loop
.EntireColumn.Hidden = True
End With
End If
Next
End Sub

Sub UnHideColums()
For Each cell_in_loop In Range("HICOLS")
If cell_in_loop.Value = 0 Then
With cell_in_loop
.EntireColumn.Hidden = False
End With
End If
Next
End Sub

All macros from this ng.

ps. I do not know VBA,