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