Solved: hide columns in other sheet based on dates match

[sunilmulay]

Hi there
Could someone please help with this? I'm a VBA newbie....

I have two sheets:A and B. Sheet A contains within columns B-Z dates in rows 2 and 3. Dates in row 2 are the start of week dates, and dates in row 3 are the end of week dates. Sheet B has a cell where the user can enter a date. I want to set a button next to it that runs a macro that hides all columns in Sheet A in range B-Z where the date the user entered is not equal to or between the start/end dates of that column.

Any thoughts?
Thanks
S

[GTO]

Hi Sunilmulay,

Are you asking for help w/the code, the button, or both?

Could you post the book or an example thereof?

Thank you so much,

Mark

[Bob Phillips]

[vba]

Public Sub Test()
Dim MatchCol As Long

With Worksheets("Sheet A")

.Columns("B:Z").Hidden = False
On Error Resume Next

If MatchCol <> 0 Then


.Columns("B:Z").Hidden = True
.Columns("A").Offset(, MatchCol).Hidden = False
Else

MatchCol = Application.Match(CLng(Worksheets("Sheet B").Range("A1").Value), .Range("B2:Z2"), 1)
If MatchCol <> 0 Then

If Worksheets("Sheet B").Range("A1").Value <= .Range("A2").Offset(1, MatchCol) Then

.Columns("B:Z").Hidden = True
.Columns("A").Offset(, MatchCol).Hidden = False
End If
End If
End If
End With
End Sub
[/vba]

[sunilmulay]

Hi Guys
thanks for the replies. i tried the above code, but couldn't get it to work - the wrong columns get hidden! Could you please check out the attached simplified file?
I want to hide columns P to V in Stage01-Actuals, where the date entered on Sheet-Internal Dashboard Cell F3 doesn't equal to or lie within the dates in rows 8 and 9 of Sheet Stage01-Actuals
Thanks in advance!!!
Sunil

[Bob Phillips]

What would happen if the date was say 29th Oct 2008?

[sunilmulay]

If the user entered 29th of Oct in the second sheet in the attached file above, all columns in the first sheet in range P to V should be hidden except for column S, because 27thOct < 29th Oct < 2nd Nov.

Thanks
S

[GTO]

Were you still looking to solve this Sunimulay? xld's code does work perfectly, you have some 'issues' with the formatting of your "date" cells.