VBA to Hide Rows if Month

[tlc53]

Hi. I just can't get this VBA code to work. Basically, if the Year End Date is March, then it needs to hide rows 71:86.
I can't get the Month = 03 bit to work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Year_End_Date")) Is Nothing Then

If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value

    Case Is = Month("*/03/*")
        Sheets("O7").Range("71:86,").EntireRow.Hidden = True
       
    Case Is <> Month("*/03/*")
        Sheets("O7").Range("71:86").EntireRow.Hidden = False

   
   End Select
   End If
 End Sub

Thank you

 

[rollis13]

Changing this part of the macro to this could be a solution:

Select Case Month(Target.Value)
    Case Is = 3
        Sheets("O7").Range("71:86").EntireRow.Hidden = True
    Case Is <> 3
        Sheets("O7").Range("71:86").EntireRow.Hidden = False
End Select

 

[Peter_SSs]

What about one of these shorter options?

If "Year_End_Date is on sheet "O7"

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = Range("Year_End_Date").Address Then Rows("71:86").Hidden = Month(Range("Year_End_Date")) = 3
End Sub

If "Year_End_Date is not on sheet "O7"

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = Range("Year_End_Date").Address Then Sheets("O7").Rows("71:86").Hidden = Month(Range("Year_End_Date")) = 3
End Sub

 

[tlc53]

Changing this part of the macro to this could be a solution:

Select Case Month(Target.Value)
    Case Is = 3
        Sheets("O7").Range("71:86").EntireRow.Hidden = True
    Case Is <> 3
        Sheets("O7").Range("71:86").EntireRow.Hidden = False
End Select

So simple once you know Thank you!!

 

[rollis13]

Thanks for the positive feedback, glad we were able to help.

 

[Peter_SSs]

Did you try the other options too?

 

[bebo021999]

If Target.Address = Range("Year_End_Date").Address Then...

The line
If Target.Address = Range("Year_End_Date").Address Then
will fail if Range("Year_End_Date") is a multi-cell range
For Ex:
Target will be in range A1:A100

 

[Peter_SSs]

The line
If Target.Address = Range("Year_End_Date").Address Then
will fail if Range("Year_End_Date") is a multi-cell range

I'd offer an alternative if the OP tries my code and reports such an issue.

 

[tlc53]

Did you try the other options too?

Hi. Apologies for the delay. Yes, it does work and I can see it's nice and simple with just the one line.
I've saved your code as I'll use it elsewhere but in this particular case, there's another code that runs straight after it. If the entity type <> Company, then those rows and more get hidden. Your code cannot be over-ridden, so it stops this second code from running.
Your code on its own though, functions perfectly. Thanks for your help!

 

[Peter_SSs]

Cheers. Thanks for testing & reporting back.