Problem with code

[Guest]

My $C$4 is a List (Jan, Feb, ...Dec)
I have a table (Range) mydates
as follows:

Name Month# Column#Start
Jan 1 3
Feb 2 5
Mar 3 7
Apr 4 9
May 5 11
Jun 6 13
Jly 7 15
Aug 8 17
Sep 9 19
Oct 10 21
Nov 11 23
Dec 12 25


Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next
Dim vcol1 As Integer
Dim vcol2 As Integer
Set mydates = Sheets("Sheet1").Range("A2:C13")
If Target.Address = "$C$4" Then
vcol1 = Application.VLookup(Target.Value, mydates, 3, False)
vcol2 = vcol1 + 1
For Each c In Range("E7:Z7")
If Val(c.Column) <> vcol1 Or c.Column <> vcol2 Then
c.EntireColumn.Hidden = True
End If
Next
End If

But the above is hiding EVERYTHING -- Trying to work out for 3 hrs... grrrrr
withlut success///

 

[Guest]

It looks like you are returning the Column#Start with your VLookup.

vcol1 = Application.VLookup(Target.Value, mydates, 3, False)

The 3 say use the 3rd col in mydates to search. Is that what you want?

 

[Guest]

Yes, Each month has two columns, for example there is a Jan Number column
then a Jan Text column, Feb Number, then Feb Text and so on...

 

[Guest]

Correction: The 3 says return the data from that column in mydates.

 

[Guest]

Jim, your Range $C$4 looks like it is within the scope of your mydates range
A2:C13, unless these ranges are on separate sheets. If they are on separate
sheets then the code needs to be modified to reflect that. Otherwise, the
code, as written is saying, if C4 is the target then return the value that is
selected. Which
really don't make much sense.

Maybe if you could explain what you are trying to accomplish I could offer a
solution.

 

[Guest]

Thanks JLGWhiz;
I found a different way to acheive my solution.
Jim