Automatically show columns based on criteria

[Scott Browner]

I need help showing only columns based on criteria. I have a spreadsheet with Q1, Q2, Q3 and Q4 data. If I type "Q1" into a specific cell (in this case cell C2 which is shaded) I want all of the cells containing Q1 at the top to appear and all of the cells that contain Q2, Q3 and Q4 to be hidden. Here is the spreadsheet that I have been working on so far. Thanks in advance for your help.

[TABLE="width: 690"]
<colgroup><col width="165" style="width: 124pt; mso-width-source: userset; mso-width-alt: 6034;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;" span="4"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;" span="4"> <tbody>[TR]
[TD="class: xl78, width: 264, bgcolor: transparent, colspan: 2"]Incentive Plan - Cumulative[/TD]
[TD="class: xl79, width: 69, bgcolor: #92D050"]Q1[/TD]
[TD="class: xl72, width: 74, bgcolor: transparent"][/TD]
[TD="class: xl77, width: 69, bgcolor: transparent"]Q1[/TD]
[TD="class: xl77, width: 69, bgcolor: transparent"]Q2[/TD]
[TD="class: xl77, width: 69, bgcolor: transparent"]Q3[/TD]
[TD="class: xl77, width: 69, bgcolor: transparent"]Q4[/TD]
[TD="class: xl77, width: 59, bgcolor: transparent"]Q1[/TD]
[TD="class: xl77, width: 59, bgcolor: transparent"]Q2[/TD]
[TD="class: xl77, width: 59, bgcolor: transparent"]Q3[/TD]
[TD="class: xl77, width: 59, bgcolor: transparent"]Q4[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl76, width: 236, bgcolor: whitesmoke, colspan: 4"]Prorated Bonus Amounts[/TD]
[/TR]
[TR]
[TD="class: xl76, width: 165, bgcolor: whitesmoke"]Working Timekeeper[/TD]
[TD="class: xl76, width: 99, bgcolor: whitesmoke"]Position[/TD]
[TD="class: xl76, width: 69, bgcolor: whitesmoke"]Date Employed[/TD]
[TD="class: xl76, width: 74, bgcolor: whitesmoke"]Date Terminated[/TD]
[TD="class: xl76, width: 69, bgcolor: whitesmoke"]Annual Salary as of EOQ1[/TD]
[TD="class: xl76, width: 69, bgcolor: whitesmoke"]Annual Salary as of EOQ2[/TD]
[TD="class: xl76, width: 69, bgcolor: whitesmoke"]Annual Salary as of EOQ3[/TD]
[TD="class: xl76, width: 69, bgcolor: whitesmoke"]Annual Salary as of EOQ4[/TD]
[TD="class: xl76, width: 59, bgcolor: whitesmoke"]Q1 Bonus[/TD]
[TD="class: xl76, width: 59, bgcolor: whitesmoke"]Q2 Bonus[/TD]
[TD="class: xl76, width: 59, bgcolor: whitesmoke"]Q3 Bonus[/TD]
[TD="class: xl76, width: 59, bgcolor: whitesmoke"]Q4 Bonus[/TD]
[/TR]
</tbody>[/TABLE]

 

[SteveO59L]

as a worksheet change event
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rng
Set rng = Range("e2:l2")
If Target.Row = 2 And Target.Column = 3 Then
Columns("d:m").Hidden = False
For Each c In rng
If c.Value <> Target.Value Then
c.EntireColumn.Hidden = True
End If
Next c
End If
End Sub

 

[Scott Browner]

Thanks so much for your help Steve.

 

[Scott Browner]

Is it possible to put a string in the target field? An example would be; I would like to look at Q1 and Q2 at the same time or Q2 and Q3. Is that possible?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address = "$C$2" Then
      Range("D:M").EntireColumn.Hidden = False
      For Each Cl In Range("e2", Cells(2, Columns.Count).End(xlToLeft))
         Cl.EntireColumn.Hidden = Not InStr(1, Target.Value, Cl.Value) > 0
      Next Cl
   End If
End Sub