Excel If statement for setting cell drop down box and hide

Question

I have a spreadsheet that tracks tasks with drop down boxes that include the values B, G ,Y, A, and N/A. The number of each status is calculated at the top of the sheet through a count if statement.

I am hoping to add in another drop down box above each section using the same as the above. So you can select N/A for a team. When selecting the N/A for the team I need a section of rows to also be set to N/A and hide the lines.

I am struggling to understand what I should be adding to set the cells to N/A - Please can someone help.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("G13") = "N/A" Then

Set cells E14-E22 TO N/A and hide rows 14 - 22

    'Range("14:22").EntireRow.Hidden = True'


    Else
    Range("14:22").EntireRow.Hidden = False

End If

End Sub

Trigger cells details are below

Target Trigger Cell G13 Cell Range Row 14 - 22

Target Trigger Cell G23 Cell Range Row 24

Target Trigger Cell g 25
Cell Range Row 26 - 27

Target Trigger Cell G28 Cell Range Row 29 - 30

Target Trigger Cell G31
Cell Range Row 32 - 34

Target Trigger Cell G35 Cell Range Row 36 - 38

Target Trigger Cell G39 Cell Range Row 30 - 41

Target Trigger Cell G42 Cell Range Row 43 - 44

Target Trigger Cell G45 Cell Range Row 46 - 49

Target Trigger Cell G50 Cell Range Row 51 -54

Target Trigger Cell G55 Cell Range Row 56 - 57

Target Trigger Cell G58 Cell Range Row 59 - 61

Target Trigger Cell G62 Cell Range Row 63 - 68

Target Trigger Cell G69 Cell Range Row 70 - 83

Target Trigger Cell G84 Cell Range Row 85 - 87

Target Trigger Cell G88 Cell Range Row 89 - 97

Target Trigger Cell G98 Cell Range Row 99 - 104

Target Trigger Cell G105 Cell Range Row 106 - 111

Target Trigger Cell G112 Cell Range Row 113 - 115

Target Trigger Cell G116 Cell Range Row 117 - 118

Target Trigger Cell G119 Cell Range Row 120 - 124

Target Trigger Cell G125
Cell Range Row 126 - 128

Target Trigger Cell G129
Cell Range Row 130 - 137

Target Trigger Cell G138 Cell Range Row 139 - 145

Target Trigger Cell G146 Cell Range Row 147

Unknown Unknown · Accepted Answer · 2018-05-27T23:28:53

If your trigger cell is G13, G23, G33, etc then,

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("G:G")) Is Nothing And _
           (Target.Row Mod 10) = 3 And _
           Target.Row >= 13 Then
        On Error GoTo meh
        Application.EnableEvents = False
        With Target.Offset(1, -2).Resize(9, 1)
            If Target.Value2 = "N/A" Then
                .Value = "N/A"
                .EntireRow.Hidden = True
            Else
                .Value = vbNullString
                .EntireRow.Hidden = False
            End If
        End With
    End If
meh:
    Application.EnableEvents = True

End Sub

Excel If statement for setting cell drop down box and hide

1 Answers