vba insert triangle in cells in Excel
Q153. In Excel, how do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?
For example, I want to show whether Sales has increased or decreased compared to previous period using Triangle / Inverted Triangle - how can I add the same using VBA?
To do it in Excel, here is the answer:
a) First the character code for Triangle and Inverted Triangle should be obtained. For that, click on "Symbol" under "Insert" as shown below. In the "Symbol" dialog box click on the Symbol of choice and note down the Character code.
In this case it is Hex 25B2 for Triangle and 25BC for Inverted Triangle.
a) Click on any cell in the Table. In the Ribbon, "Table Tools" Design tab appears. Click on "Total Row" under "Table Style Options". "Total Row" appears at the end of the Table.
b) Use the Hex codes to fill the cell appropriately with Triangle or Inverted Triangle using ChrW(CharCode in Hex) function after applying the condition using VBA (see macro below).
- Option Explicit
- Sub InsertSymbols()
- Dim i As Integer
- 'Loop through Table
- For i = 5 To 14
- If ActiveSheet.Range("AB" & i) > ActiveSheet.Range("AC" & i) Then
- 'Sales decreased compared to last period - use Inverted Triangle.
- ActiveSheet.Range("AD" & i) = ChrW(&H25BC)
- 'Sales increased compared to last period - use Triangle.
- ActiveSheet.Range("AD" & i) = ChrW(&H25B2)
- End If
- Next i
- End Sub
Result after Macro execution: