hiding rows in excel based on words in a cell

Question

I am VERY new at VBA and have been searching for VBA code that can do what I want. I have tried several and attempted to adapt them however I just can't seem to get them right so thought I would try for some help!

I have 6 projects that have either YES or NO in the cell next to them indicating if the person is working on that project. This is determined by the persons name next to that project on another sheet, so is formula produced and not a drop down or typed in value.

The project then has a few rows below corresponding to it.

If there is a NO next to a project (in C6), I want the corresponding rows for that project to be hidden (rows 13:29).

I want this to be repeated for each project,

so a no in c7 hides 31:47, a no in C8 hides 49:65, a no in C9 hides 67:83, a no in C10 hides 85:101, a no in C11 hides 103:118,

I don't know if this is possible and have been going around in circles, really hope that someone can help :)

this is one of the adaptions i have tried but i am sure i am doing something wrong, sorry for not posting this here before

Private Sub Worksheet_Change(ByVal Target As Range) 

If Target.Address = "$C$6" Then 
If Target.Value = NO Then 
Rows(13:29).EntireRow.Hidden = True 
Else 
Rows(13:29).EntireRow.Hidden = False 

If Target.Address = "$C$7" Then 
If Target.Value = NO Then 
Rows(31:47).EntireRow.Hidden = True 
Else 
Rows(31:47).EntireRow.Hidden = False 
End If 
End If 


If Target.Address = "$C$8" Then 
If Target.Value = NO Then 
Rows(49:65).EntireRow.Hidden = True 
Else 
Rows(49:65).EntireRow.Hidden = False 
End If 
End If 

If Target.Address = "$C$9" Then 
If Target.Value = NO Then 
Rows(67:83).EntireRow.Hidden = True 
Else 
Rows(67:83).EntireRow.Hidden = False 
End If 
End If 


If Target.Address = "$C$10" Then 
If Target.Value = NO Then 
Rows(85:101).EntireRow.Hidden = True 
Else 
Rows(85:101).EntireRow.Hidden = False 

End If 
End If 



If Target.Address = "$C$11" Then 
If Target.Value = NO Then 
Rows(103:119).EntireRow.Hidden = True 
Else 
Rows(103:119).EntireRow.Hidden = False 

End If 
End If 


End Sub 

Answer 1

Shortest code I can think of:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim bHide As Boolean
    bHide = (InStr(1, Target.Value, "NO", vbTextCompare) > 0)
    Select Case Target.Address
        Case "$C$6"
            Rows("13:29").EntireRow.Hidden = bHide
        Case "$C$7"
            Rows("31:47").EntireRow.Hidden = bHide
        Case "$C$8"
            Rows("49:65").EntireRow.Hidden = bHide
        Case "$C$9"
            Rows("67:83").EntireRow.Hidden = bHide
        Case "$C$10"
            Rows("85:101").EntireRow.Hidden = bHide
        Case "$C$11"
            Rows("103:119").EntireRow.Hidden = bHide
    End Select
End Sub

Please test.

Answer 2

so a no in c7 hides 31:47, a no in C8 hides 49:65, a no in C9 hides 67:83, a no in C10 hides 85:101, a no in C11 hides 103:118,

C11 hides 103:118? Shouldn't this be C11 hides 103:119? Let me explain.

To make your work simple, you need to find a trend on how your code would progress. See this analysis.

You are looking for "No" in row 6 and showing/hiding from row 13:29. Similarly you are looking for "No" in row 7 and showing/hiding from row 31:47. So if you notice then there is a trend. The difference between the row is 18. See image above. And If there is a trend then there is a possibility of a loop!

And because of this simple table I could also figure out that what you said C11 hides 103:118 is incorrect and probably a typo. The 118 had to be 119 to maintain that difference of 18

So your code can actually be condensed to (Untested)

Dim i As Long, StartRow As Long, EndRow As Long

StartRow = 13
EndRow = 29

For i = 6 To 11
    If UCase(Range("C" & i).Value) = "NO" Then
        Rows(StartRow & ":" & EndRow).EntireRow.Hidden = True
    Else
        Rows(StartRow & ":" & EndRow).EntireRow.Hidden = False
    End If
    StartRow = StartRow + 18
    EndRow = EndRow + 18
Next i

The next thing is that since the NO's are getting changed by formula, you will have to use Worksheet_Calculate and not Worksheet_Change until and unless the formula is picking up values from the same sheet and the cells which are changing C6:C11 are manually changed by a user.