Hide/unhide columns

[prem1611]

Hi,

i have following data


From A3:E15 I have a table with current year data(CYR) and from G3: K15 with CYR Year To Date(YTD) data.</SPAN></SPAN>

In A1 I have “YTD” and in G1 I have “CYR”</SPAN></SPAN>

If I click on “YTD” column A:E should hide and column G:K should appear. </SPAN></SPAN>
If I click on “CYR” column G:K to hide and Column A:E to appear</SPAN></SPAN>

I used the following code but it solves only half the problem</SPAN></SPAN>

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN></SPAN>
If Range("d1").Value = "YTD" Then</SPAN></SPAN>
Columns("B:L").EntireColumn.Hidden = True</SPAN></SPAN>
Else</SPAN></SPAN>
Columns("B:L").EntireColumn.Hidden = False</SPAN></SPAN>
End If</SPAN></SPAN>
End Sub


Any ideas pls</SPAN>


Prem</SPAN>

 

[James Snyder]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value = "YTD" Then
        Columns("B:L").EntireColumn.Hidden = True
    Else
        Columns("B:L").EntireColumn.Hidden = False
    End If
If Range("D1").Value = "CYR" Then
        Columns("G:K").EntireColumn.Hidden = True
    Else
        Columns("A:E").EntireColumn.Hidden = False
    End If
End Sub

You are passing in the target as a range, but never using it. Although I can see you are setting the hidden property, I do not see what would trigger the call to the Sub. You might want to use events. Look at:

[COLOR=Green]'Declaration[/COLOR] [COLOR=Blue]Event[/COLOR] SelectionChange [COLOR=Blue]As[/COLOR] DocEvents_SelectionChangeEventHandler</pre>

 

[prem1611]

This code hides all the colums excluding A1

 

[prem1611]

I have used following code,

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D1").Value = "YTD" Then
Columns("B:L").EntireColumn.Hidden = True
Columns("M:W").EntireColumn.Hidden = False
End If
If Range("N1").Value = "CYR" Then
Columns("M:W").EntireColumn.Hidden = True
Columns("B:L").EntireColumn.Hidden = False
End If
End Sub

but in this code, IF in "D1" i give YTD, column B:L is not hiding and M:W is opening
Whereas if in "N1" i give CYR, column M:W is hiding and B:L is opening

How to change the code to make the first condition work?

Any ideas pls