Uploaded July 2019 | Updated September 2026, 2 weeks ago

---

How to automate formatting of data with symbols using VBA. Here's the complete VBA code:
Option Explicit

Sub formatData()

Dim i As Long, lastrow As Long, totalp As Single, totaln As Single, total As Single

lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

totalp = 0
totaln = 0

For i = 2 To lastrow

If Cells(i, 2) "not equal to" 0 And (Cells(i, 3) - Cells(i, 2)) / Cells(i, 2) greater than 0 Then

Cells(i, 5) = ((Cells(i, 3) - Cells(i, 2)) / Cells(i, 2)) * 100

totalp = totalp + Round(Cells(i, 5), 2)

Cells(i, 5) = ChrW(&H25B2) & " " & Round(Cells(i, 5), 2) & "%"

Cells(i, 5).Font.ColorIndex = 10

ElseIf Cells(i, 2) "not equal to" 0 And (Cells(i, 3) - Cells(i, 2)) / Cells(i, 2) less than 0 Then

Cells(i, 5) = ((Cells(i, 3) - Cells(i, 2)) / Cells(i, 2)) * 100

totaln = totaln + Round(Cells(i, 5), 2)

Cells(i, 5) = ChrW(&H25BC) & " " & Round(Cells(i, 5), 2) & "%"

Cells(i, 5).Font.ColorIndex = 3

End If

total = totaln + totalp

Next i

MsgBox "The total is " & total & "%"

End Sub
A downloadable sample file for practice is available at our website: exceltrainingvideos.com/automate-formatting-of-data-with-symbols