Completed, Thanks for the Assistance. At first when I tried this I was getting a Run Time Error "13", I then added an If Error statement and that got rid of the error.
The code below will Display certain columns based on the value in column C. If you click on a blank cell, then it displays all columns. If you click on the header at the top, then it displays all columns, now for the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B:$B" Then
Exit Sub
End If
On Error Resume Next
Select Case Target.Value
Case ""
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = False
Case "(Header Value)"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = False
Case "(Value A)"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = True
Columns("J").Hidden = True
Columns("K").Hidden = False
Case "(Value B)"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = True
Columns("E").Hidden = True
Columns("F").Hidden = True
Columns("G").Hidden = True
Columns("H").Hidden = True
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = True
End Select
End Sub
Thanks to all who assisted. This was done in Excel Version 2002.
Quote:
Originally Posted by Rick Rothstein \(MVP - VB\)
This works, but only for one cell. I need it to work for the entire
column from B2 down. My guess is I need to use a range and modify this
line, but can not figure out what to modify it with:
If Target.Address < "$B$1" Then
How do I modify this to apply to all of column B from B2 down? Thanks.
I'm not sure I understand what you mean by "apply to all of column B from B2
down". Do you mean you want a ValueA or ValueB setting in ANY cell of column
B (except B1) to trigger the hiding of the columns you indicated? If so,
what about when some of the cells in column B are set to one value and the
remaining cells are set to the other value.. which cell governs the hiding
operation? Can you explain what you are trying to do in a little more
detail?
Rick
|