Is there a way to apply the macro code to all the rows in a column? Instead of typing out the code for each row

8 Views Asked by At

Hi I have a code as below, used for stock count:

`Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C15")) Is Nothing Then
Application.EnableEvents = False
    Range("D15") = Range("D15").Value - Range("C15").Value
Application.EnableEvents = True
End If
End Sub`

Basically, Cell C15 is the value the user will key in, which is the outgoing stock quantity. Cell D15 is the Stock Quantity Balance.See snapshot

Every time the user type a value into cell C15, The value in cell D15 will deduct the value to give the stock balance. Is there a simpler way to apply this code for the rows from 15 onwards? Instead of keying the code for each row like below:

`Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("C15")) Is Nothing Then
Application.EnableEvents = False
    Range("D15") = Range("D15").Value - Range("C15").Value
Application.EnableEvents = True
End If

If Not Intersect(Target, Range("C17")) Is Nothing Then
Application.EnableEvents = False
    Range("D17") = Range("D17").Value - Range("C17").Value
Application.EnableEvents = True
End If

If Not Intersect(Target, Range("C18")) Is Nothing Then
Application.EnableEvents = False
    Range("D18") = Range("D18").Value - Range("C18").Value
Application.EnableEvents = True
End If

If Not Intersect(Target, Range("C19")) Is Nothing Then
Application.EnableEvents = False
    Range("D19") = Range("D19").Value - Range("C19").Value
Application.EnableEvents = True
End If

If Not Intersect(Target, Range("C20")) Is Nothing Then
Application.EnableEvents = False
    Range("D20") = Range("D20").Value - Range("C20").Value
Application.EnableEvents = True
End If
End Sub`

So on so forth.. Hope you get what I mean.

0

There are 0 best solutions below