Hide Columns Depending on Result of a Cell

[Excelor]

Okay, I am new to a lot of this, so this may be simple.

I am looking for a formula or code that will hide columns depending on the content of a certain cell.

I found some code on an earlier post but that only worked when the cell was selected and manually changed.

What I have is a cell, say L1, that has a formula which based on the result of other cells will either return a "Y" or an "N". When this returns an "N" I want columns D:I to be hidden. When it is a "Y" the columns will be visible.

Hopefully that makes sense, hope someone can help!!

Thanks

 

[Michael M]

Something like this, pasted into the sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Columns.EntireColumn.Hidden = False
If Range("L1").Value = "N" Then Columns("D:I").EntireColumn.Hidden = True
End Sub

 

[Smitty]

Welcome to the Board!

For a formula you can use the Calculate event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> rng1 <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rng = Range("L1")<br>    <SPAN style="color:#00007F">Set</SPAN> rng1 = Range("D:I")<br>    <br>    Cells.EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> rng.Value = "N" <SPAN style="color:#00007F">Then</SPAN><br>        rng1.EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        rng1.EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[My Aswer Is This]

Try this:
Right Click on sheet tab
Choose View code
Paste the below code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("L1") = "N" Then
Worksheets("Sheet1").Columns("D:I").Hidden = True
Else
Worksheets("Sheet1").Columns("D:I").Hidden = False
End If
End Sub

 

[farmerscott]

Excelor,

Welcome to Mr Exel.

The following code will fire as the changes are made in your cell. Paste the code in the sheet module relevant to the sheet you want the changes to happen.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Set rng = Sheets("Sheet1").Columns("D:I")
    rng.EntireColumn.Hidden = False
If Range("$L$1").Value = "Y" Then
rng.EntireColumn.Hidden = True
End If

End Sub

hope that helps,

FarmerScott

 

[Michael M]

@Scott
This line needs to be "N"

If Range("$L$1").Value = "Y" Then

 

[farmerscott]

Thanks Michael for the catch. Putting on the golf gear today?

 

[Michael M]

Yep.....had a 72 today....on the way back, Yay !!!

 

[Excelor]

Thanks for all the replies! I will get on it later and let you know how I get on.

Major beginner here, so you will probably see a lot more coming from this one!

Cheers again!

 

[Excelor]

Something like this, pasted into the sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Columns.EntireColumn.Hidden = False
If Range("L1").Value = "N" Then Columns("D:I").EntireColumn.Hidden = True
End Sub

This works perfectly. I thought at first it wasn't but then when I clicked into any cell on the Sheet it hid/showed the columns accordingly. Just what I needed. I take it this fires whenever any cell is selected on the given Sheet right?

Can I ask aswell, this didn't seem to work when I was using Excel 2007 at home. Was it maybe something I was doing wrong or will it not work in certain versions of Excel?

Thanks again,

Mike