[SOLVED] How do you update the same cell you've entered a value in, based on another cell?

How do you update the same cell you've entered a value in, based on another cell?

Complete VBA beginner here - I'm trying to update a cell, which I've just entered a value for, based on another cell and its changing value.

So, I enter 100 into A1.

Then, based on the option of typing 3 words in cell C5, I want to multiple A1 by a certain amount.

If I enter 'Normal' into C5, it'll multiple A1 by 1. If I enter 'Low' into C5, it'll multiple A1 by 0.5. If I enter 'High' into C5, it'll multiple A1 by 2.

Any help or direction would be great :)

Solution

You need a worksheet event handler that triggers when cell C5 value changes

place this code in worksheet code pane (just right click on the tab and select "View Code")

Dim myValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address <> "$A$1" Then myValue = Range("A1").Value ' keep track of any "original value" changes
    If Target.Address <> "$C$5" Then Exit Sub

    If IsEmpty(myValue) Then myValue = Range("A1").Value ' retrieve the "original value" if not already set

    On Error GoTo SafeExit
    Application.EnableEvents = False
    Select Case Target.Value
        Case "Normal"
            ' no need to multiply by 1 ...

        Case "Low"
            Range("A1").Value = myValue * 0.5 ' divide "original value"

        Case "High"
            Range("A1").Value = myValue * 2 'multiply "original value"

    End Select

SafeExit:
    Application.EnableEvents = True       
End Sub