Hide columns based on value in cell

[kapilg89]

Hi,

I want to hide unhide columns based on the criteria given in the cell
for eg: my cell a1 has programme life eg 5 years, so the columns for only five years should be visible and the rest of the columns, viz the rest 10 years, should be auto hidden.

Thanks in advance......

 

[kapilg89]

Just one more thing if i want to link the cell to a different sheet the would the code be any different?
Also i wanted a live updation of the macro. How to Achieve it?

 

[kapilg89]

how can i change the range "A1" to cell "H19" in sheet "Basic Info"

 

[Sektor]

Change Range("A1") to Range("H19") everywhere in the code.

 

[kapilg89]

The link is not in the same sheet it is in a different sheet namely "Basic Info"

 

[Sektor]

Then you should put code into "Basic info" sheet module and prefix with sheet those ranges which refer to C-Q range.

 

[kapilg89]

I cannot find a way to link the sheet wherein the columns need hiding
Can you hel me
The sheet wherein the number of year is present is named "Basic Info" and the sheet wherein the columns need to be hidden is named as "Kit List and Volumes"

 

[Sektor]

In "Basic Info" sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)     If Target.Address = Range("A1").Address Then
        With Sheets("Kit List and Volumes")
            .Cells.EntireColumn.Hidden = False
            If Target.Value < 15 Then .Range(.Cells(1, Target.Value + 3), .Cells(1, 17)).EntireColumn.Hidden = True
        End With     End If End Sub</pre>

 

[kapilg89]

The code contains a compilation error it dosent seem to work

 

[Sektor]

It's because of incorrect forum's code formatting. Couldn't you fix it yourself?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        With Sheets("Kit List and Volumes")
            .Cells.EntireColumn.Hidden = False
            If Target.Value < 15 Then .Range(.Cells(1, Target.Value + 3), .Cells(1, 17)).EntireColumn.Hidden = True
        End With
    End If
End Sub

 

[kapilg89]

Now it worked!!!Thanks a lot for your help