How to add Icons from "conditional formattingIcon sets" to individual cells?

Question

I'm trying to add to individual cells the icons from "Conditional Formatting -> Icon sets" in Excel (the traffic lights, or the rhomboid shapes, etc.) to individual cells.

I've only found this in the documentation: https://docs.microsoft.com/en-us/office/vba/api/excel.iconset But I cannot find any example code where a condition is not necessary.

Let's say I would like to add the yellow circle to cell "A1", how could I do this?

Thanks!

user_1140579 · Answer

Let's say I would like to add the yellow circle to cell "A1", how could I do this?

As @Pᴇʜ mentioned that these icons are from icon sets and cannot be used without conditional formatting. Having said that we can fake the traffic lights.

Logic

There is a symbol which you can use from the Insert | Symbol menu. It looks like as shown below
Simply insert the symbol at the begining of the cell text and change the font to Wingdings and Forecolor to relevant color. Feel free to set the font size as well.

Code

Option Explicit

Const TrafficLightSignal As String = "l"

Sub Sample()
    Dim ws As Worksheet
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    
    InsertTrafficLightSignal ws.Range("A1"), -16727809 '<~~ Orange
    InsertTrafficLightSignal ws.Range("A2"), -11489280 '<~~ Green
    InsertTrafficLightSignal ws.Range("A3"), -16776961 '<~~ Red
End Sub

Private Sub InsertTrafficLightSignal(rng As Range, SignalColor As Long)
    Dim aCell As Range
    
    For Each aCell In rng
        With aCell
            '~~> You can add a space between them as well if you want
            '.Value = TrafficLightSignal & " " & aCell
            .Value = TrafficLightSignal & aCell

            With .Characters(Start:=1, Length:=1).Font
                .Name = "Wingdings"
                .FontStyle = "Regular"
                .Size = 17 '<~~ Change Font size as applicable
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .Color = SignalColor '<~~ Color is changed here
                .TintAndShade = 0
                .ThemeFont = xlThemeFontNone
            End With
        End With
    Next aCell
End Sub

In Action

Alternative icons

You can also chose this icon if you want

For this, in the above code use these instead

Const TrafficLightSignal As String = "n"

and

.Name = "Webdings" 'instead of Wingdings

Output:

Whatever symbol you choose ensure it is black (i.e filled up) so that the font color can be changed.

How to add Icons from "conditional formatting>Icon sets" to individual cells?

1 Answers