Unhide Columns Containing Month and year

[howard]

I have several sheets containing the Month and Year in B4:M4 (Sheet3 to Sheets11)


I have set up months and year on sheet "Months" and linked this to Cell A2 using data validation


I have written code so that if for eg Oct 2018 is selected from the drop down list, that the column containing Oct 2018 on for eg sheet3 , then the col containing oct 2018 will be unhidden, but cannot get this to work i.e nothing happens


It would be appreciated if someone could assist me is resolving this

 Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Long
Dim c As Range

Application.ScreenUpdating = False

    If Target.Address(0, 0) <> "A2" Then Exit Sub
    Columns("B:M").Hidden = False
    Set c = Range("B4:M4").Find(Target, LookIn:=xlValues, LookAt:=xlPart)
    Columns("B:M").Hidden = True

    If Not c Is Nothing Then
    c.EntireColumn.Hidden = False
    Else
    MsgBox Target & " not found", 48, "ERROR"
    End If

Application.ScreenUpdating = True

End Sub

 

[jmacleary]

Hi there. I have tried your code in a simple sheet just looking for the content of A2 as text, and it works fine. I presume that this code is in the worksheet code area of each Sheet3 to Sheet11. If that is the case, then I suspect that not all your cells are the same format and so the find isn't working. Just to check this out, temporarily change the format of A2 and B4 to M4 to a number, and see what they are actually set to.

 

[howard]

Thanks for your input. Will check & amend where necessary