Home > Mobile >  Every New Value in a Column to Automatically Multiply by X
Every New Value in a Column to Automatically Multiply by X

Time:03-03

In a spreadsheet, I am trying to somehow make it so any new numerical value that gets inputted into a cell in column D to multiply by 85%. Basically, if I go to any cell 2-100,000 in column D and input a numerical value, I want it to automatically show 85% of it.

If I input '100' into D5, I want it to show '85'.

If I input '200' into D317, I want it to show '170'.

Is this possible to do in any way?

Manually multiplying by another cell or by 0.85 can't be used.

Thank you so much!

CodePudding user response:

Worksheet Change: Modify Any Input in a Column

Sheet Module e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim irg As Range
    With Range("D2") ' from 'D2' to the bottom-most row:
        Set irg = Intersect(.Resize(Rows.Count - .Row   1), Target)
    End With
    If irg Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    Dim iCell As Range
    For Each iCell In irg.Cells
        If IsNumeric(iCell.Value) Then
            iCell.Value = 0.85 * iCell.Value
        End If
    Next iCell
    
    Application.EnableEvents = True

End Sub
  • Related