VBA Excel - run macro base on several cells value (multiple cells)-CodePudding

I have some fast (or at least I think so) question about VBA. I have a code attached below which works pretty well, but I must modify it a bit - and get errors all the time.

What I must achiev is: testing not only cell L7, but also L8 separately, so in practice:

if value of L7 is a run macro_a (this is working well) but at the same time, when value of L8 is k I must run macro_k or if the L8 is m, macro_m etc

how it can be done ?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L7")) Is Nothing Then
If Target.Value = "a" Then macro_a
If Target.Value = "b" Then macro_b
If Target.Value = "c" Then macro_c
End If
End Sub

CodePudding user response：

This code will check if either, or both, cells L7/L8 have been changed and then run the appropriate macros based on which cell(s) have changed and the value(s) in the cell(s).

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range

    If Not Intersect(Target, Range("L7:L8")) Is Nothing Then
        For Each cl In Target
            If cl.Address(0, 0) = "L7" Then
                Select Case cl.Value
                    Case "a":
                        macro_a
                    Case "b"
                        macro_b
                    Case "c"
                        macro_c
                End Select
            End If
            If cl.Address(0, 0) = "L8" Then
                Select Case cl.Value
                    Case "k"
                        macro_k
                    Case "m"
                        macro_m
                End Select
            End If
        Next cl
    End If

End Sub