3

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!

danklok
  • 33
  • 2

1 Answers1

3

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thank you! How do I make it so it only applies for let's columns 2 to 1,000? – danklok Mar 03 '22 at 03:12
  • I have modified my answer in the meantime. Instead of `Rows.Count` use any acceptable positive number (`<=1048576 = Rows.Count`, or `<=65536 = Rows.Count` if prior Excel 2007), e.g. `1000`. – VBasic2008 Mar 03 '22 at 03:22