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.
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:
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:
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:
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.
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.
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é.
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:
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:
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