Excel Conditional Format Icons, without Conditional Format


I want to occupy the icons of the conditional format, such as the green and red flag; but without occupying the conditional format. The idea is:

If numero <= 0 Then
    'rt.Interior.Color = RGB(0, 255, 0)
    'Aquí va una Bandera Verde
    'rt.Interior.Color = RGB(255, 0, 0)
    'Aquí va una Bandera Roja
End If

I can not occupy the conditional format since the value of the cell is something like "Bank1 = 123456", besides the 123456 is the result of a rather complex formula.

In summary, how do I use, call or reference those Conditional Format icons?

asked by Jedicillo 20.12.2016 в 22:31

1 answer


Conditional formatting icons only apply to cells with numeric values , and there is no way to insert those icons from code.

A simple alternative is to create a new column next to it, which extracts only the number of your values. For example, assuming that all your values follow the nombre = valor format, we can:

  • Find the position where the " = " is found.

    ENCONTRAR(" = ";A1)

    * It is assumed that the cell with the value is in A1

  • Get the text after that position, until the end

    EXTRAE(A1; ENCONTRAR(" = ";A1) + 3; 9999)
  • Convert everything to number

    =VALOR(EXTRAE(A1;ENCONTRAR(" = ";A1) + 3;9999))

  • On this new column, it is easy to establish a conditional format with icons, selecting only the values between which they apply:

    If you still want to do it from code:

    Sub FormatoConBanderas()
        'Rangos a aplicar
        Dim celdaInicial As Range
        Dim columnaIconos As Range
        Set celdaInicial = Range("A1")
        Set columnaIconos = Range("B1:B6")
        With columnaIconos
            With .FormatConditions(.FormatConditions.Count)
                .ShowIconOnly = True
                .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
                'Rojo negativos
                .IconCriteria(1).Icon = xlIconRedFlag
                'Rojo para el cero
                With .IconCriteria(2)
                    .Type = xlConditionValueNumber
                    .Value = 0
                    .Operator = 7
                    .Icon = xlIconRedFlag
                End With
                'Verde positivos
                With .IconCriteria(3)
                    .Type = xlConditionValueNumber
                    .Value = 0
                    .Operator = 5
                    .Icon = xlIconGreenFlag
                End With
            End With
        End With
    End Sub
    answered by 29.12.2016 / 21:01