Sumar y contar celdas por color de formato condicional

Hace algunos meses publique el artículo Operaciones con colores en Excel y de inmediato recibí preguntas sobre la posibilidad de utilizar dichas funciones para evaluar los colores aplicados a una celda a través del formato condicional.

Las funciones creadas en ese artículo evalúan la propiedad Interior.Color que tiene cada una de las celdas en Excel, sin embargo, el formato condicional no utiliza dicha propiedad sino que tiene su propia “versión” para almacenar el color de fondo de una celda. Por esa razón no es posible utilizar dichas funciones para evaluar colores establecidos a través de una regla de formato condicional.

Así que hoy crearemos una nueva función VBA (UDF) para sumar y contar celdas por color de formato condicional. Pero antes de iniciar con el código hablaremos un poco sobre los objetos y propiedades que debemos evaluar para obtener el color de fondo proveniente de un regla de formato condicional.

La colección FormatConditions

Primero debemos recordar que un mismo rango de celdas en Excel puede estar sujeto a varias reglas de formato condicional al mismo tiempo, así que para guardar esa lista de formatos condicionales se creó la colección FormatConditions en VBA la cual enumera todas las reglas de formato condicional aplicadas en un rango.

Para comprender el funcionamiento de esta colección haremos un ejemplo. Considera la siguiente lista de números en el rango A1:A10 donde he aplicado una regla de formato condicional que resalta en color rojo los valores mayores a 750.

Sumar y contar por color de formato condicional

Ahora insertaré un botón de comando ActiveX en la misma hoja y colocaré el siguiente código VBA en su evento Click:

Private Sub CommandButton1_Click()
    MsgBox Range("A1:A10").FormatConditions.Count
End Sub

La única línea de código ejecutada se encargará de mostrar el valor de la propiedad Count de la colección FormatConditions la cual contiene el recuento de las reglas de formato condicional para el rango indicado. Al hacer clic sobre el botón de comando se mostrará el siguiente mensaje:

Sumar celdas por color de formato condicional

El mensaje nos indica que el rango A1:A10 tiene una sola regla de formato condicional. Ahora crearé una segunda regla para el mismo rango que resaltará de color verde todas las celdas con un valor menor a 250. Una vez creada la regla de formato condicional, volveré a pulsar el botón de comando y el número mostrado en el mensaje habrá aumentado debido a la nueva regla creada:

Contar celdas por color de formato condicional

Es así como la colección FormatConditions nos permite obtener información sobre las reglas de formato condicional aplicadas a un rango de celdas. Ahora centraremos nuestra atención a una propiedad específica de dicha colección.

La propiedad FormatCondition.Interior.Color

Cada regla de formato condicional almacena el estilo que aplicará a las celdas que cumplan con las condiciones establecidas y específicamente el color de relleno se almacena en la propiedad Interior.Color. Para demostrar el valor de esta propiedad agregaré un nuevo botón de comando con el siguiente código:

Private Sub CommandButton2_Click()
    For i = 1 To Range("A1:A10").FormatConditions.Count
        MsgBox "Regla " & i & vbLf & _
            "Color: " & Range("A1:A10").FormatConditions(i).Interior.Color
    Next i
End Sub

El código anterior recorre todos los elementos de la colección FormatConditions y para cada elemento mostrará un mensaje con el número de regla y su color de relleno que está almacenado en la propiedad Interior.Color. Al pulsar el botón obtengo el siguiente resultado:

Suma de celdas por color de formato condicional en Excel

El color devuelto será un valor entre 0 y 16777215 que corresponde a una de las combinaciones de colores primarios (rojo, verde y azul) que se pueden formar en Excel. Si quieres saber un poco más sobre los colores en Excel consulta el artículo Evaluar el color de fondo de una celda.

Los colores mostrados con el código anterior son los colores pertenecientes a cada una de las reglas de formato condicional y no el color de una celda específica. Para conocer el color de formato condicional aplicado a una celda será necesario encontrar la regla que se cumple sobre dicha celda para entonces obtener el color correspondiente.

Macro para obtener el color de una celda

El desafío más grande al crear una macro para obtener el color de una celda es descubrir la regla de formato condicional que está activa. Para eso utilizamos un bucle For Next que recorrerá toda la colección de formatos haciendo una evaluación de cada regla para descubrir si está activa.

Function COLORFC(Celda As Range) As Long

'Indicará si la relga de formato condicional está activa
Dim ReglaActiva As Boolean

'Recorrer todas las reglas de formato condicional para la celda indicada
For i = 1 To Celda.FormatConditions.Count

    'Evaluar la regla FormatConditions(i)
    With Celda.FormatConditions(i)
    
    'Si la regla está basada en el valor de la celda
    If .Type = xlCellValue Then
    
    'Identificar el operador de la regla y evaluar si está activa
    Select Case .Operator
    Case xlBetween:      ReglaActiva = Celda.Value >= Evaluate(.Formula1) _
                         And Celda.Value <= Evaluate(.Formula2)
    Case xlNotBetween:   ReglaActiva = Celda.Value <= Evaluate(.Formula1) _
                         Or Celda.Value >= Evaluate(.Formula2)
    Case xlEqual:        ReglaActiva = Evaluate(.Formula1) = Celda.Value
    Case xlNotEqual:     ReglaActiva = Evaluate(.Formula1) <> Celda.Value
    Case xlGreater:      ReglaActiva = Celda.Value > Evaluate(.Formula1)
    Case xlLess:         ReglaActiva = Celda.Value < Evaluate(.Formula1)
    Case xlGreaterEqual: ReglaActiva = Celda.Value >= Evaluate(.Formula1)
    Case xlLessEqual:    ReglaActiva = Celda.Value <= Evaluate(.Formula1)
    End Select
        
    'Si la regla es una expresión (Fórmula)
    ElseIf .Type = xlExpression Then
    
        Application.ScreenUpdating = False
        Celda.Select
        ReglaActiva = Evaluate(.Formula1)
        Range(ActiveCell.Address).Select
        Application.ScreenUpdating = True
    
    End If
    
    'Devolver el color si la regla está activa
    If ReglaActiva Then
        COLORFC = .Interior.Color
        Exit Function
    End If
    
    End With
Next i

End Function

Las primeras líneas de código se encargan de recorrer todas las reglas de formato condicional para la celda proporcionada. La primera validación importante es sobre la propiedad Type que nos permite saber el tipo de regla que estamos analizando, ya sea del tipo xlCellValue o del tipo xlExpression. Es importante mencionar que existen más tipos de reglas de formato condicional, de hecho la enumeración XlFormatConditionType contiene 14 tipos diferentes, pero solamente los dos tipos mencionados anteriormente tienen una manera de ser evaluados desde VBA. Por esta razón la función COLORFC considera en primer lugar las reglas de formato condicional que evalúan directamente el valor de una celda.

Sumar por colores de formato condicional en Excel

El cuadro de diálogo anterior muestra precisamente la lista de reglas de formato condicional que son del tipo xlCellValue y que por lo tanto serán reconocidas por nuestra función. Ahora hagamos una prueba con los datos de ejemplo para ver cómo la función COLORFC devuelve el color aplicado a cada celda de acuerdo a la regla de formato condicional activa. Recuerda que las reglas creadas resaltan de rojo las celdas con un valor superior a 750  y de color verde los valores menores a 250.

Contar celdas con formato condicional

Nuestra función VBA funciona correctamente al devolver el código de color adecuado para cada celda. El otro tipo de reglas consideradas en la función COLORFC son aquellas basadas en una expresión, que generalmente son las reglas basadas en una fórmula que contiene funciones de Excel. Pero debo darte una mala noticia, esta opción solo funcionará si tenemos Excel en inglés. Y a continuación explico por qué.

Sumar y contar celdas con color de relleno de formato condicional

Al momento de crear una regla de formato condicional que utilice una función de Excel, colocaremos su nombre en castellano. Dicha fórmula se almacenará en una propiedad de la regla de formato condicional y al momento de evaluarla desde VBA la cadena de texto contendrá el nombre de la función en español lo cual ocasionará un error ya que VBA solamente entiende las funciones de Excel por su nombre en inglés. Aún así he dejado esta funcionalidad porque existe un porcentaje de usuarios que utilizan Excel en inglés y ellos podrán obtener el color de las celdas que utilicen reglas de formato condicional basadas en fórmulas.

Después de todas estas aclaraciones (que eran necesarias), tenemos una función que nos devolverá el color aplicado a una celda desde una regla de formato condicional, así que ahora solo debemos utilizarla para sumar o contar celdas por color.

Sumar por color de formato condicional

Para sumar por color de formato condicional crearé una nueva función VBA de la siguiente manera:

Function SUMARPORCOLORFC(CeldaColor As Range, Rango As Range) As Double

Dim Celda As Range
Dim Total As Double
Dim Color As Long

Color = COLORFC(CeldaColor)

For Each Celda In Rango.Cells
    If COLORFC(Celda) = Color Then
        Total = Total + Celda.Value
    End If
Next Celda

SUMARPORCOLORFC = Total

End Function

El primer argumento de la función es una celda que contiene el color por el cual deseamos sumar, así que una de las primeras acciones es obtener dicho color con la función COLORFC. Posteriormente se hará un recorrido por todo el rango de celdas para sumar el valor de aquellas que tengan el mismo color. Observa cómo trabaja nuestra función con los datos de ejemplo:

Sumar celdas por color de fondo de formato condicional en Excel

La función devuelve correctamente la suma de las celdas A1 y A9 que son las que tienen el mismo color que la celda A1 que ha sido indicada como el primer argumento de la función.

Contar por color de formato condicional

Si en lugar de sumar deseamos contar las celdas que tienen un mismo color, será suficiente con modificar la función anterior para que en lugar sumar el valor de la celda se sume el valor 1 por cada celda con el color indicado. El código de esta nueva función es el siguiente:

Function CONTARPORCOLORFC(CeldaColor As Range, Rango As Range) As Integer

Dim Celda As Range
Dim Total As Integer
Dim Color As Long

Color = COLORFC(CeldaColor)

For Each Celda In Rango.Cells
    If COLORFC(Celda) = Color Then
        Total = Total + 1
    End If
Next Celda

CONTARPORCOLORFC = Total

End Function

Al utilizar esta función con los mismos datos de ejemplo podrás observar que la función CONTARPORCOLORFC nos devuelve el valor 2 que corresponde a las celdas con el mismo color que la celda A3:

Contar celdas que tengan un color de formato condicional

Descarga el libro de trabajo para comenzar a sumar y contar celdas por color de formato condicional en tus propios libros de trabajo. Solo recuerda que las funciones creadas en este artículo validarán solo las reglas de formato condicional basadas en el valor de la celda. También recuerda que podrás evaluar reglas basadas en fórmulas solamente si tienes una versión en inglés de Excel.

Artículos relacionados
Operaciones con colores en Excel