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

Question

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 :)

Answer 1

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

Answer 2

Why don't you display the result in another cell like B1?
Then you could just use a simple formula there:

=A1*IF($C$5="Low",0.5,IF($C$5="High",2,1))

---

Alternate VBA solution

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AffectedCells As Range
    Set AffectedCells = Intersect(Target, Me.Range("A1")) 'you can extend the range to more cells like A1:A10

    If Not AffectedCells Is Nothing Then
        On Error GoTo SafeExit

        Dim Factor As Double
        Select Case Me.Range("C5").Value 'note this is case sensitive
            Case "Normal": Exit Sub
            Case "Low":    Factor = 0.5
            Case "High":   Factor = 2
        End Select

        Application.EnableEvents = False
        Dim Cell As Range
        For Each Cell In AffectedCells.Cells
            If IsNumeric(Cell.Value) Then
                Cell.Value = Cell.Value * Factor
            End If
        Next Cell
    End If

SafeExit:
    Application.EnableEvents = True
End Sub