VBA Code to hide and unhide based on a cell value

[Silver13]

I am trying to create a Macro that takes cell B2 (which is a formula, to show as either Law or Non Law) and hides lines 39-43 on the same sheet if B2 is "Non Law". Any assistance would be greatly appreciated.

[stnkynts]

Do you want it to update when B2 changes? If so we need to know what cells and linked to the formula in B2. Or are you wanting to have a button that you can press that hides/unhides based upon value in B2?

[Silver13]

Yes I need the rows to hide / unhide based on what is in B2. The formula in B2 is: =IF(CCBNonLawApplies<>"","Non Law",IF(CCBLawApplies<>"","Law",""))
Basically, if the user selects a field in the first tab of the workbook, it will fill in B2 with Law or Non Law. I need the Macro to hide rows 39-43 of B2 shows Non Law. Trying to have this automatic to avoid the use of a button if possible.
Thank you!

[stnkynts]

What is CCBNonLawApplies and CCBLawApplies? Note: The reason I am asking this is because the worksheet_change event will not recognize a change in formula so I need to track down what range actually gets changed.

[Silver13]

It is on the first tab of my workbook. The user will either select applies in Law or Applies in Non Law both are name boxes.
LAW_Non Law.jpg

the selections are in B59 or D59. B59 the user selects "Applies" for Non Law in D59 the user can select "Applies" for Law. If the user selects Non Law on the GeneralInfo_ADMIN tab, then B2 on the SORA tab will show Non Law and the rows will need to be hidden. If the user selects Law on the GeneralInfo_ADMIN tab, then B2 on the SORA tab will show Law and the rows need to show.

Thank you so much for your assistance!

[stnkynts]

So by selecting "Applies" in cell B59 the formula in B2 should be updated to "Non-Law" at which time you want to hide lines 39-43?

Please Login or Register  to view this content.

[Silver13]

Yes, by selecting "Applies" in B59 of the prior tab (tab 1), B2 in the next tab (tab 2) will be updated to "Non Law" and at that time I need lines 39 - 43 to be hidden in tab 2. Thank you so much for all of your help @stnkynts.

[stnkynts]

Modify my submitted code to reference tab 2.

Please Login or Register  to view this content.

[Silver13]

I am getting a Compile error: Expected End Sub

[Silver13]

I figured out the Compile Error and have updated the code as follows:

Please Login or Register  to view this content.

But the macro doesnt hide the rows when the event changes. I have tested it and selected "Non Law" and the rows are not hidden.

[stnkynts]

Submit your workbook

[Silver13]

I had the code in a module, instead of my sheet 1. I also updated the code to work based on D59 as well.

Please Login or Register  to view this content.

Thank you for all of your help with this!