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:
