Hiding rows using MACROS

[Brent R Janetzki]

Hi, I am trying to hide rows based on a a specific value selcted in a drop down list. For example I have a drop down list of "Yes" and "No" in Cell C42. When I click on Yes I am wanting rows 45 to 47 to remain but when I click on No I want them to hidden. I've spent hours trying to work this out but I keep getting Syntax Errors and Code 438 for some reason. Talk about frustrating. Any help would be appreciated.

Thanks Brent

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C42" Then
      Rows("45:47").Hidden = Target.Value = "No"
   End If
End Sub

 

[Brent R Janetzki]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C42" Then
      Rows("45:47").Hidden = Target.Value = "No"
   End If
End Sub

Your a genius. Thank you

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Brent R Janetzki]

Glad to help & thanks for the feedback.

Another question... It works when I have it open. I then save as a macro-enabled workbook and close the file but when I click on it to reopen it doesn't work. Any ideas on how to overcome this?

 

[Fluff]

Do you mean when you change the value in C42 nothing happens?

 

[Brent R Janetzki]

Do you mean when you change the value in C42 nothing happensY

Do you mean when you change the value in C42 nothing happens?

Yes, it's like the macro is become disabled or something.

 

[Fluff]

Is the code still in the relevant sheet module?