Conditional formatting to hide rows

[Erica2261]

Is there a way to hide rows in a spreadsheet based on the results in a given cell within each row? For example, if the result cell C6 is "inactive" can the entire row be hidden (and the same for all other rows in the range)?

I don't know how to code so I am trying to do this with a formula through conditional formatting. I am using Excel 2002.

Thanks!
Erica

 

[VoG]

You will need code for that.

What is the entire range and what causes a cell to contain "inactive" - do you enter that or does C6 contain a formula?

 

[Erica2261]

Thanks -

C6 contains the following formula:

=IF(OR(F7>$B$3,G7< $B$3),"inactive","active")

Range is A6:Z11

I am reasonably good with spreadsheets but never coded before so not sure code will help me!

Erica

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Dim i As Long
For i = 6 To 11
    Rows(i).Hidden = Range("C" & i).Value = "inactive"
Next i
End Sub

then press ALT + Q to close the code window. Press F9 to force a recalculation to see the effect.

 

[Erica2261]

VoG -

That was really cool and worked great. ..I have a follow-up question though. My spreadsheet is designed so that users can enter a date into cell $B$3 which drives whether the formula in C6 returns "active" or "inactive".

When I used your code with a date of 6/30/10 the inactive rows were hidden (which is great); however, when I changed the date to 8/6/10 (which flipped a cell from inactive to active) the row stayed hidden. Can I modify the code to unhide the row if the status changes?

Erica

 

[Erica2261]

VoG -

Please ignore prior posting. Not sure what I did, but it is working as intended now!

Thank you very much.
Erica