Hide and unhide row based on value of cell

[rintelen]

Hi

Here's a tricky one. My spreadsheet has a value in cell AO25 which either is 1 or 0 depending on a formulae.

When AO25 =1 then I want to hide ROW 8

When AO25 = 0 then I want to hide ROW 9

I'd prefer a macro that just ran this continually checking it, and altering it whenever the value of AO24 altered.

Is this possible? the macro would have to start working when the excel spreadsheet was launched.

 

[repairman615]

Hello,

Maybe this could help.

Right click the sheet tab.
Veiw Code.
Paste into the sheet module:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>        <br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range("AO25").Value + 1<br>        <SPAN style="color:#00007F">Case</SPAN> 1<br>        Rows("9").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>        Rows("8").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">Case</SPAN> 2<br>        Rows("8").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>        Rows("9").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>       <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



Jeff

 

[rintelen]

This didn't work. It went into a perpetual loop.

 

[klarowe]

Will the value be anything other than 1 or 0? If not then you can use this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("AO25").Value = 1 Then
        Rows(8).Hidden = True
        Rows(9).Hidden = False
    End If
    If Range("AO25").Value = 0 Then
        Rows(8).Hidden = False
        Rows(8).Hidden = True
    End If
End Sub

 

[repairman615]

This didn't work. It went into a perpetual loop.

There is no Loop. The calculate event is triggered from when the worksheet calculates.

This did work when I tested. AO25 changed via formula, and when changed the rows were hidden.

Maybe this may help to work:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br><br>Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br><br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range("AO25").Value + 1<br>        <SPAN style="color:#00007F">Case</SPAN> 1<br>        Rows("9").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>        Rows("8").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">Case</SPAN> 2<br>        Rows("8").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>        Rows("9").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>       <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>