How to automate formatting of data with symbols using VBA. Often we need to present data that is visually appealing. Instead of black and white numbers we can have symbols and coloured fonts to represent data which can be quickly and easily understood. Below is an image that shows how the percentage change of a scrip can be represented using coloured triangles:

In the video that follows we describe how to format data in the usual manual manner and then describe how to automate the formatting of the graphics,

Here’s the complete VBA code to format geometric shapes automatically:

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) <> 0 And (Cells(i, 3) – Cells(i, 2)) / Cells(i, 2) > 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) <> 0 And (Cells(i, 3) - Cells(i, 2)) / Cells(i, 2) < 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

You can download a sample file for practice: