Mac – Excel, how to run multiple macros for individual cells in a worksheet

macrosmicrosoft excel

I've got a code to work for only cells D3 and E3, I need it to do the same for other cells like D4/E4, D5/E5 but with different formulae on the same worksheet. I'm trying to make new private subs for the other cells but they do not seem to compile? Thanks in advance:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DE As Range, t As Range, v As Variant
    Dim r As Long
    Set t = Target
    Set DE = Range("D3:E3")
    If Intersect(t, DE) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r = t.Row
        v = t.Value
        If v = "" Then
            Range("D" & r & ":E" & r).Value = ""
        End If
        If IsNumeric(v) Then
            If Intersect(t, Range("E3:E3")) Is Nothing Then
                t.Offset(0, 1).Value = v * 0.0393701
            Else
                t.Offset(0, -1).Value = v / 0.0393701
            End If
        End If
    Application.EnableEvents = True
End Sub

Best Answer

Fixed it now:

Private Sub Worksheet_Change(ByVal Target As Range) Dim BC As Range, t As Range, v As Variant Dim r As Long Set t = Target Set BC = Range("D:E") If Intersect(t, BC) Is Nothing Then Exit Sub Application.EnableEvents = False r = t.Row v = t.Value

    If IsNumeric(v) Then
        If Intersect(t, Range("E3")) Is Nothing Then
            Range("E3").Formula = Range("D3") * 0.0393701

        Else
            Range("D3").Formula = Range("E3") / 0.0393701

        End If

         If Intersect(t, Range("E4")) Is Nothing Then
            Range("E4").Formula = Range("D4") * 3.28084

        Else
            Range("D4").Formula = Range("E4") / 3.28084

        End If

            If Intersect(t, Range("E5")) Is Nothing Then
            Range("E5").Formula = Range("D5") * 14.5038

        Else
            Range("D5").Formula = Range("E5") / 14.5038

        End If

                        If Intersect(t, Range("E6")) Is Nothing Then
            Range("E6").Formula = Range("D6") * 14.5038

        Else
            Range("D6").Formula = Range("E6") / 14.5038

        End If

                        If Intersect(t, Range("E7")) Is Nothing Then
            Range("E7").Formula = Range("D7") * 14.5038

        Else
            Range("D7").Formula = Range("E7") / 14.5038

        End If

                        If Intersect(t, Range("E9")) Is Nothing Then
            Range("E9").Formula = Range("D9") * 0.02628

        Else
            Range("D9").Formula = Range("E9") / 0.02628

        End If

                        If Intersect(t, Range("E10")) Is Nothing Then
            Range("E10").Formula = Range("D10") * 35.3147

        Else
            Range("D10").Formula = Range("E10") / 35.3147

        End If

 End If
Application.EnableEvents = True

End Sub

Related Question