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......

 

[Sektor]

Well, where those columns do start from?

 

[kapilg89]

C to Q

 

[Sektor]

In worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        [COLOR=#ff0000]Cells.EntireColumn.Hidden = False[/COLOR]
        Range(Cells(1, Range("A1") + 3), Cells(1, 17)).EntireColumn.Hidden = True
    End If
End Sub

 

[kapilg89]

Sorry for the novice question but should i insert a new module and then paste the above code of should i just paste it in the this workbook excel object

 

[Sektor]

No, the worksheet module already exists. Open VBA editor, and on the left side you will see all your sheets' modules (with names in parenthesis).

 

[kapilg89]

Thanks the VB Code works fine. Except one thing that the hidden cells do not hide on entering a bigger cell value after entering a smaller value

 

[Sektor]

Since C-Q is total 15 columns, I put limiter:

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

 

[kapilg89]

Worked like a charm
Thanks buddy for the simplest of the solution!!

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?

 

[Sektor]

I changed code from "<=" to "<". Otherwise 15-th columns doesn't show up!