excel - 如何将 "conditional formatting>Icon sets"中的图标添加到单个单元格？

Question

我正在尝试将 Excel 中“条件格式 -> 图标集”中的图标(交通灯或菱形等)添加到单个单元格中。
我只在文档中找到了这个:https://docs.microsoft.com/en-us/office/vba/api/excel.iconset
但是我找不到任何不需要条件的示例代码。
假设我想将黄色圆圈添加到单元格“A1”，我该怎么做？
谢谢!

Answer 1

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

正如@Pᴇʜ 提到的，这些图标来自图标集，没有条件格式就不能使用。话虽如此，我们可以伪造红绿灯。
逻辑

您可以使用 中的一个符号插入 |符号 菜单。如下图所示

只需在单元格文本的开头插入符号并将字体更改为 翅膀 和 前色 到相关的颜色。也可以随意设置字体大小。

代码

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

在行动

替代图标
如果需要，您也可以选择此图标

为此，在上面的代码中使用这些代替

Const TrafficLightSignal As String = "n"

和

.Name = "Webdings" 'instead of Wingdings

输出 :

无论您选择什么符号，都要确保它是黑色的(即填充)，以便可以更改字体颜色。