Multiple VBA Worksheet Change Events

Question

I'm still new to macros and trying to combine two change events into one successfully, any help would be appreciated!

First Change Event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G7:G9")) Is Nothing Then
    Select Case Range("G7:G9")
        Case "Individual": Macro1
        Case "Company": Macro2
        Case "Trust": Macro3
        Case "": Macro4
      End Select
End If

End Sub

Second Change Event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Range("G9")
        Case "Income Tax Return" Or "Financial Accounts" Or "": Macro5
        Case "FBT": Macro6
        Case "BAS/IAS": Macro7
        Case "Contractor Reporting" Or "Workers Compensations" Or "Payroll Tax" Or "STP / PAYGW": Macro7
    End Select
End If

End Sub

The macros being called hide and unhide respective sheets when a list present the selected term.

Thanks!

Answer 1

One way could be handled by a couple of nested Select Case within a For each

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim specialRange As Range
    Dim cell As Range

    Set specialRange = Me.Range("G7,G9")

    ' Exit if doesn't intersect (this could be just an If Not wrapping the For Each
    If Intersect(Target, specialRange) Is Nothing Then Exit Sub

    For Each cell In specialRange

        ' First handle the cell address
        Select Case cell.Address
        Case "$G$7"
            ' Then its value
            Select Case cell.Value
            Case "Individual"
                'Macro1
            Case "Company"
                'Macro2
            Case "Trust"
                'Macro3
            Case vbNullString
                'Macro4
            Case Else
                'DoSomething?
            End Select

        Case "$G$9"
            Select Case cell.Value
            Case "Income Tax Return", "Financial Accounts", vbNullString
                'Macro5
            Case "FBT"
                'Macro6
            Case "BAS/IAS"
                'Macro7
            ' As the next case is calling the same macro as the previous, your could merge them
            Case "Contractor Reporting", "Workers Compensations", "Payroll Tax", "STP / PAYGW"
                'Macro7
            Case Else
                'DoSomething?
            End Select
        End Select



    Next cell

End Sub

Let me know if it works