hiding rows based on a selection (text) in a specific cell

[Blanchetdb]

I found the following coding but I would like to know to adapt it to text

[TABLE="width: 384"]
<colgroup><col width="64" style="width: 48pt;" span="8"> <tbody>[TR]
[TD="width: 384, bgcolor: transparent, colspan: 6"]Private Sub Worksheet_Change(ByVal Target As Range)
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 8"] If Target = "Indeterminate" Or "Term" Or "Transfer" Or "As Required" Then Exit Sub
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 5"] If Target.Address(0, 0) <> "C36" Then Exit Sub[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 6"] Sheets("Sheet1").Rows("95:103").Hidden = Target.Value = 0
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]End Sub[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

What I am looking for is where the person would select an option in Cell C36 ( example - Term) and if so, rows 95-103 would hide. There are a number of other options that would require the rows to hide as well

 

[James006]

Hello,

Initially .. you could test the following :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$36" Then Exit Sub
  If Target = "Term" Then
    Rows("95:103").Hidden = True
  Else
    Rows("95:103").Hidden = False
  End If
End Sub

HTH

 

[Blanchetdb]

Thank you

that worked.....awesome

if I have other options such as "Indeterminate", "Transfer", etc....How and where would I add that to the code?

I tried: If Target = "Term" Or "Indeterminate" Or "Transfer" Or "Student" Or "Term extension" Or "As required" Or "Assignment" Then...... but it didn't work

 

[James006]

At least ... we are heading in the right direction ...

To hard-code a long list of strings ...which should normally be located in a data validation list ...

you could test following :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$36" Then Exit Sub
Dim arCases As Variant
Dim res As Variant
arCases = Array("Term", "Indeterminate", "Transfer", "Student", "Term extension", "As required", "Assignment")


  res = Application.Match(Target, arCases, 0)
  If IsError(res) Then
       Rows("95:103").Hidden = False
  Else
       Rows("95:103").Hidden = True
  End If
End Sub

Hope this will help

 

[Blanchetdb]

it worked perfectly......thanks a lot

 

[James006]

Glad you could fix your problem ...

Thanks ... for your Thanks ...