July 20, 2019

Calculate Change Percentage Automatically as you Type

How to calculate percentage change automatically as you type using worksheet change event with the intersect method in VBA. Watch the video:

Here’s the complete VBA code to get the percentage difference:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long

lastrow = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

If Not Intersect(Target, Range(“C2:C” & lastrow)) Is Nothing Then

If Cells(Target.Row, 2) <> “” And Cells(Target.Row, 2) <> 0 Then
Cells(Target.Row, 4) = (Cells(Target.Row, 3) – Cells(Target.Row, 2)) / Cells(Target.Row, 2)
Cells(Target.Row, 4).NumberFormat = “0.0%”
End If

End If

End Sub