如何在不使用条件格式的情况下使用VBA将符号/图标格式化为单元格 [英] How can I use VBA to format Symbols / Icons into cells without using conditional formatting

问题描述

我正在使用VBA代码放置条件格式以覆盖大表中的值，我每个单元格使用2个公式来确定要使用3个符号中的哪个。我需要根据列来检查具有不同单元格的每个单元格的值，因此就我理解不足而言，我必须将条件格式设置规则分别放在每个单元格上，以确保每个单元格中的公式正确。这是因为条件格式不能使用相对地址，您必须给它每个单元格的确切地址...对吗？

I am using VBA code to place conditional formatting to cover values in a large table, I use 2 formulae per cell to determine which of the 3 symbols to use. I need to check the value of each cell with a different cell depending on the column and therefore as far as I understamd, I have to place my conditional formatting rule on each cell individually to ensure the formula is correct in each. This is because conditional formatting cannot take relative addresses, you have to give it the exact address of each cell ... correct?

大量条件格式实例的速度正在减慢我的计算机在很大程度上。

The large number of conditional formatting instances is slowing my computer to a huge extent.

是否可以在不使用条件格式的情况下将条件格式使用的符号放置到单元格中？

Is it possible to place symbols used by conditional formatting, into a cell, without using conditional formatting?

也许有点像图像，但是保留了下面的单元格值，可以使用条件格式进行设置。

Perhaps somewhat like an image, but whilst retaining the cell value underneath, as can be done using conditional formatting.

下面，我给出了用于放置条件格式的代码。

Below I have given the code I use to put the conditional formatting in place. Any help is very much appreciated!!

    Dim AIs As Range
    Dim rng As Range
    Dim cl As Range

    Set AIs = ActiveSheet.Range("Table")
    For Each cl In AIs.Columns
        For Each rng In cl.Cells

        rng.FormatConditions.AddIconSetCondition
        rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
        With rng.FormatConditions(1)
            .ReverseOrder = False
            .ShowIconOnly = True
            .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
        End With

         With rng.FormatConditions(1).IconCriteria(1)
            .Icon = xlIconYellowExclamationSymbol
        End With
        With rng.FormatConditions(1).IconCriteria(2)
            .Icon = xlIconRedCross

            .Type = xlConditionValueFormula
            .Value = "=IF(VALUE(LEFT(" & rng.Parent.Cells(5, rng.Column).Address & _
                  ";1)=0;1;6)"

            .Operator = 7
        End With
        With rng.FormatConditions(1).IconCriteria(3)
            .Icon = xlIconGreenCheck

            .Type = xlConditionValueFormula
            .Value = "=IF(VALUE(LEFT(" & rng.Address & ";1))<=VALUE(LEFT(" & _
                  rng.Parent.Cells(5, rng.Column).Address & ";1));1;6)"

            .Operator = 7
        End With
        Next rng
    Next cl

推荐答案

直接向单元格中添加形状：

Adding a shape directly to a cell:

Dim cLeft As Single
Dim cTop As Single

cLeft = rng.Left
cTop = rng.Top

with AIs.Shapes.AddShape(msoShapeOval, cLeft, cTop, 12, 12)
    .ForeColor.RGB = RGB(255, 0, 0)
    'Other properties can be found at
    'http://msdn.microsoft.com/en-us/library/office/bb251480%28v=office.12%29.aspx
end with

您可能需要调整cTop和cLeft以及宽度/高度，以根据需要定位圆

you may want to adjust cTop and cLeft, and the width/height to position the circle as you wish

这篇关于如何在不使用条件格式的情况下使用VBA将符号/图标格式化为单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！