Hide/Unhide rows based on cell value (result of formula)

I just can't figure this out, even after looking through examples. Any help is greatly appreciated.

Also, looking for visual basic code for sheet.

A bit of VBA. Go to the VBA IDE (alt-F11), and paste this code in the worksheet module you want to hide/unhide on:

Public Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Not Intersect(Target, [G16]) Is Nothing Then
        Application.EnableEvents = False
        Range("16:17").EntireRow.Hidden = [G16].Value = 0
        Range("36:37").EntireRow.Hidden = [G16].Value = 0
        Application.EnableEvents = True
    End If

End Sub

You might have to go to the immediate window (ctrl-g), and enable events for this to work. Just type in:

application.enableevents = true

and press enter within the immediate window.

Thank you, this helps; can anyone tell me what the additional code is so that the rows will unhide when the value is a 1? Needs to be able to "toggle" back and forth based on the value in G16, which contains a formula. Thanks again for any help.

Originally Posted by wallyeye

A bit of VBA. Go to the VBA IDE (alt-F11), and paste this code in the worksheet module you want to hide/unhide on:

Public Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Not Intersect(Target, [G16]) Is Nothing Then
        Application.EnableEvents = False
        Range("16:17").EntireRow.Hidden = [G16].Value = 0
        Range("36:37").EntireRow.Hidden = [G16].Value = 0
        Application.EnableEvents = True
    End If

End Sub

You might have to go to the immediate window (ctrl-g), and enable events for this to work. Just type in:

application.enableevents = true

and press enter within the immediate window.

Thank you, I think this helps; can you tell me what the additional code is so that the rows will unhide when the value is a 1 and hide again when the value is a 0? Needs to be able to "toggle" back and forth based on the value in G16, which contains a formula. Thanks again for any help.

You will need to change the second line:

If Not Intersect(Target, [G16]) Is Nothing Then

to look at the cells that the formula looks at, rather than G16. So if G16 is:

=if(and(a1=2,b1=4),1,2)

then the line would need to change to:

If not intersect(target, [a1]) is nothing _
or not intersect(target, [b1]) is nothing then

so that it checks the actual values that change, rather than a calculation.

Last question: The unhide part? How can I get this to unhide again when value is 1? Thanks again!

It compares the value of G16 to 0, when G16 is equal it will hide the row, when G16 is something other than 0, it should show the row.