Problem with VBA

[Andy_H1965]

Hi there

I'm having an issue with a VBA macro- the first half works, however the second 'rule' isn't (in bold type below). I've scoured the forums but can't find an answer.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C23")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "Contractor", "Panel Builder"
ActiveSheet.Rows("63:66").Hidden = False
Case "OEM", "System Integrator"
ActiveSheet.Rows("63:66").Hidden = True
ActiveSheet.Rows("48:49").Hidden = False
ActiveSheet.Rows("51").Hidden = True
End Select

If Intersect(Target, Range("C9")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "Amend"
ActiveSheet.Row("55").Hidden = False
Case "New", "Close", "Transfer"
ActiveSheet.Row("55").Hidden = True
End Select




End Sub


So, basically trying to hide row 55 if the value of cell C9 meets criteria, but it just won't work for me.

Any ideas?? I'm probably missing something really simple but struggling to find the answer. Thanks in advance

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("C23")) Is Nothing Then
      Select Case Target.Value
         Case "Contractor", "Panel Builder"
            Rows("63:66").Hidden = False
         Case "OEM", "System Integrator"
            Rows("63:66").Hidden = True
            Rows("48:49").Hidden = False
            Rows("51").Hidden = True
      End Select
   ElseIf Not Intersect(Target, Range("C9")) Is Nothing Then
      Select Case Target.Value
         Case "Amend"
            Row("55").Hidden = False
         Case "New", "Close", "Transfer"
            Row("55").Hidden = True
      End Select
   End If
End Sub

 

[Norie]

Your code will exit here if any cell other than C23 has been changed.

If Intersect(Target, Range("C23")) Is Nothing Then Exit Sub

So the code to check the value of C9 is never reached.

There are various ways to fix this, here's one.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C9, C23")) Is Nothing Then Exit Sub

    If Target.Row = 9 Then
        Select Case Target.Value
            Case "Amend"
                Me.Rows("55").Hidden = False
            Case "New", "Close", "Transfer"
                Me.Rows("55").Hidden = True
        End Select
    Else
    
        Select Case Target.Value
            Case "Contractor", "Panel Builder"
                Me.Rows("63:66").Hidden = False
            Case "OEM", "System Integrator"
                Me.Rows("63:66").Hidden = True
                Me.Rows("48:49").Hidden = False
                Me.Rows("51").Hidden = True
        End Select
    End If
    
End Sub

 

[Andy_H1965]

Thanks, that makes sense. All good except row 55 is now hidden regardless of which dropdown option I select in cell C9

 

[Fluff]

Whose code are you referring too?

 

[Andy_H1965]

Your code will exit here if any cell other than C23 has been changed.

If Intersect(Target, Range("C23")) Is Nothing Then Exit Sub

So the code to check the value of C9 is never reached.

There are various ways to fix this, here's one.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C9, C23")) Is Nothing Then Exit Sub

    If Target.Row = 9 Then
        Select Case Target.Value
            Case "Amend"
                Me.Rows("55").Hidden = False
            Case "New", "Close", "Transfer"
                Me.Rows("55").Hidden = True
        End Select
    Else
    
        Select Case Target.Value
            Case "Contractor", "Panel Builder"
                Me.Rows("63:66").Hidden = False
            Case "OEM", "System Integrator"
                Me.Rows("63:66").Hidden = True
                Me.Rows("48:49").Hidden = False
                Me.Rows("51").Hidden = True
        End Select
    End If
    
End Sub

Thanks, that makes sense. All good except row 55 is now hidden regardless of which dropdown option I select in cell C9