VBA Change Event Calulation

Question

I am trying to use the Worksheet Change Event in Excel VBA, but it doesn't seem to working how I thought it would.

I basically want to calculate a cells value (Q2) when the value of another cell (R2) is changed or vise versa.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("O:R")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    If Target.Column = 3 Then
    'User has changed something in column Q:
        Target.Offset(0, 1).Value = Cells(2, 3) * Cells(2, 1)
    If Target.Column = 4 Then
    'User has changed something in column R:
        Target.Offset(0, -1).Value = Cells(2, 3) / Cells(2, 1)
    End If

    Application.EnableEvents = True
End Sub

Answer 1

Don't avoid working with multiple cells as the Target. Intersect can quickly parse down even deleting several full columns to the appropriate range and further restrict to the worksheet's UsedRange.

Add error control, especially to the division operation. A blank cell in A2 will quickly choke the calculation on a 'divide-by-zero'.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'deal with multiple cells as bel;ow; don't avoid them
    'If Target.Cells.Count > 1 Then Exit Sub

    'use the Intersect to determine if relevant cells have been chanmged
    'note: columns Q:R, not O:R and restrict to the used range
    If Not Intersect(Target, Target.Parent.UsedRange, Range("Q:R")) Is Nothing Then
        On Error GoTo Safe_Exit
        Application.EnableEvents = False
        Dim trgt As Range
        For Each trgt In Intersect(Target, Target.Parent.UsedRange, Range("Q:R"))
            Select Case trgt.Column
                Case 17
                    'guard against multiplying a number by text
                    If Not IsError(Cells(2, 3).Value2 * Cells(2, 1).Value2) Then
                        trgt.Offset(0, 1) = Cells(2, 3).Value2 * Cells(2, 1).Value2
                    End If
                Case 18
                    'guard against possible #DIV/0! error and divding a number by text
                    If Not IsError(Cells(2, 3).Value2 / Cells(2, 1).Value2) Then
                        trgt.Offset(0, -1) = Cells(2, 3).Value2 / Cells(2, 1).Value2
                    End If
            End Select
        Next trgt
    End If  

Safe_Exit:
    Application.EnableEvents = True
End Sub

I'm pretty sure that the actual calculation should involve a variable like trgt.Row but your posted calculation only used C2 and A2 as static cell references to divide/multiply against each other.

Answer 2

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("O:R")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    'If Target.Column = 17 Then 'CHANGED HERE!
        'User has changed something in column Q:
         'Target.Offset(0, 1).Value = Cells(2, 3) * Cells(2, 1)
    'End If
    'If Target.Column = 18 Then 'CHANGED HERE!
        'User has changed something in column R:
        'Target.Offset(0, -1).Value = Cells(2, 3) / Cells(2, 1)
    'End If

    ' I leave the If-versions above for info, but Select Case is better sometimes
    Select Case Target.Column
    Case 17 ' column Q
        Target.Offset(0, 1).Value = Cells(2, 3) * Cells(2, 1)
    Case 18 ' column R
        Target.Offset(0, -1).Value = Cells(2, 3) / Cells(2, 1)
    End Select

    Application.EnableEvents = True
End Sub

Q Column is number 17, and Column R is number 18 as shown above.