Uso del Formato Condicional con Excel VBA

Formato Condicional de Excel

El Formato Condicional de Excel le permite definir reglas que determinan el formato de las celdas.

Por ejemplo, puede crear una regla que resalte las celdas que cumplen ciertos criterios. Los ejemplos incluyen:

• Números que están dentro de un rango determinado (por ejemplo, menos de 0).
• Los 10 primeros elementos de una lista.
• Creación de un «mapa de calor».
• Reglas «basadas en fórmulas» para prácticamente cualquier formato condicional.

En Excel, el formato condicional se encuentra en la cinta de opciones en Inicio > Estilos (ALT > H > L).

Para crear su propia regla, haga clic en «Nueva regla» y aparecerá una nueva ventana:

 

Formato Condicional en VBA

Se puede acceder a todas estas funciones de formato condicional utilizando VBA.

Tenga en cuenta que cuando configure el formato condicional desde el código VBA, sus nuevos parámetros aparecerán en la ventana de formato condicional del front-end de Excel y serán visibles para el usuario. El usuario podrá editar o eliminar estos parámetros a menos que usted haya bloqueado la hoja de trabajo.

Las reglas de formato condicional también se guardan cuando se guarda la hoja de trabajo.

Las reglas de formato condicional se aplican específicamente a una hoja de trabajo concreta y a un rango de celdas determinado. Si se necesitan en otra parte del libro de trabajo, deben configurarse también en esa hoja de trabajo.

Usos Prácticos del Formato Condicional en VBA

Es posible que tengas una gran cantidad de datos brutos importados en tu hoja de trabajo desde un archivo CSV (valores separados por comas), o desde una tabla o consulta de la base de datos. Esto puede fluir a través de un tablero o informe, con números cambiantes importados de un período a otro.

Cuando un número cambia y está fuera de un rango aceptable, es posible que desee resaltarlo, por ejemplo, con el color de fondo de la celda en rojo, y puede hacerlo configurando el formato condicional. De este modo, el usuario se verá atraído al instante por este número y podrá investigar por qué está ocurriendo.

Puede utilizar VBA para activar o desactivar el formato condicional. Puede utilizar VBA para borrar las reglas de un rango de celdas, o volver a activarlas. Puede haber una situación en la que hay una razón perfectamente buena para un número inusual, pero cuando el usuario presenta el tablero o el informe a un nivel superior de gestión, quiere ser capaz de eliminar las «campanas de alarma».

Además, en los datos importados sin procesar, es posible que desee resaltar los números que son ridículamente grandes o ridículamente pequeños. El rango de datos importados suele tener un tamaño diferente para cada período, por lo que puede utilizar VBA para evaluar el tamaño del nuevo rango de datos e insertar un formato condicional sólo para ese rango.

También puede darse la situación de que haya una lista ordenada de nombres con valores numéricos en cada uno de ellos, por ejemplo, el salario de un empleado o las notas de un examen. Con el formato condicional, puedes usar colores graduados para ir de mayor a menor, lo que se ve muy impresionante para propósitos de presentación.

Sin embargo, la lista de nombres no siempre tendrá un tamaño estático, y puede utilizar código VBA para actualizar la escala de colores graduados según los cambios en el tamaño del rango.

Un Ejemplo Sencillo de Creación de un Formato Condicional en un Rango

Este ejemplo establece un formato condicional para un rango de celdas (A1:A10) en una hoja de cálculo. Si el número en el rango está entre 100 y 150 entonces el color de fondo de la celda será rojo, de lo contrario no tendrá ningún color.

Sub EjemploFormatoCondicional()
    'Definir Rango
    Dim MyRange As Range
    Set MyRange = Range("A1:A10")

    'Eliminar el formato condicional existente del rango
    MyRange.FormatConditions.Delete

    'Aplicar el formato condicional
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=100", Formula2:="=150"
    MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

Observa que primero definimos el rango MyRange para aplicar el formato condicional.

A continuación borramos cualquier formato condicional existente para el rango. Esto es una buena idea para evitar que se añada la misma regla cada vez que se ejecute el código (por supuesto no será apropiado en todas las circunstancias).

Los colores están dados por valores numéricos. Es una buena idea usar la notación RGB (Rojo, Verde, Azul) para esto. Puede usar constantes de color estándar para esto, por ejemplo vbRed, vbBlue, pero está limitado a ocho opciones de color.

Hay más de 16,7 millones de colores disponibles, y usando RGB puedes acceder a todos ellos. Esto es mucho más fácil que tratar de recordar qué número va con cada color. Cada uno de los tres números de color RGB va de 0 a 255.

Tenga en cuenta que el parámetro ‘xlBetween’ es inclusivo, por lo que los valores de celda de 100 o 150 satisfarán la condición.

Formato Multi-Condicional

Es posible que quiera establecer varias reglas condicionales dentro de su rango de datos para que todos los valores de un rango estén cubiertos por diferentes condiciones:

Sub EjemploFormatoCondicionalMultiple()
    Dim MyRange As Range
    'Crear objeto rango
    Set MyRange = Range("A1:A10")
    'Borrar formatos condicionales previos
    MyRange.FormatConditions.Delete
    'Agregar primera regla de formato condicional
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=100", Formula2:="=150"
    MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
    'Agregar segunda regla de formato condicional
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=100"
    MyRange.FormatConditions(2).Interior.Color = vbBlue
    'Agregar tercera regla de formato condicional
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=150"
    MyRange.FormatConditions(3).Interior.Color = vbYellow
End Sub

Este ejemplo configura la primera regla como antes, con el color de la celda en rojo si el valor de la celda está entre 100 y 150.

A continuación se añaden dos reglas más. Si el valor de la celda es menor que 100, entonces el color de la celda es azul, y si es mayor que 150, entonces el color de la celda es amarillo.

En este ejemplo, hay que asegurarse de que se cubren todas las posibilidades de números, y que las reglas no se solapan.

Si las celdas en blanco están en este rango, entonces se mostrarán en azul, porque Excel todavía las toma como si tuvieran un valor menor que 100.

La forma de evitar esto es añadir otra condición como expresión. Esto necesita ser añadido como la primera regla de condición dentro del código. Es muy importante cuando hay múltiples reglas, conseguir el orden de ejecución correcto, de lo contrario los resultados pueden ser impredecibles.

Sub Ejemplo2_FormatoCondicionalMultiple()
    Dim MyRange As Range
    'Crear objeto rango
    Set MyRange = Range("A1:A10")
    'Borrar formatos condicionales previos
    MyRange.FormatConditions.Delete
    'Agregar primera regla de formato condicional
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=LEN(TRIM(A1))=0"
    MyRange.FormatConditions(1).Interior.Pattern = xlNone
    'Agregar segunda regla de formato condicional
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=100", Formula2:="=150"
    MyRange.FormatConditions(2).Interior.Color = RGB(255, 0, 0)
    'Agregar tercera regla de formato condicional
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=100"
    MyRange.FormatConditions(3).Interior.Color = vbBlue
    'Agregar cuarta regla de formato condicional
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=150"
    MyRange.FormatConditions(4).Interior.Color = RGB(0, 255, 0)
End Sub

Esto utiliza el tipo de xlExpression, y luego utiliza una fórmula estándar de Excel para determinar si una celda está en blanco en lugar de un valor numérico.

El objeto FormatConditions es parte del objeto Range. Actúa de la misma manera que una colección con el índice comenzando en 1. Puede iterar a través de este objeto usando un bucle For…Next o For…Each.

Eliminación de una Regla

A veces, puede ser necesario eliminar una regla individual en un conjunto de múltiples reglas si no se ajusta a los requisitos de los datos.

Sub EliminarFormatoCondicional()
    Dim MyRange As Range
    'Crear objeto rango
    Set MyRange = Range("A1:A10")
    'Borrar formatos condicionales previos
    MyRange.FormatConditions.Delete
    'Agregar primera regla
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=100", Formula2:="=150"
            MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
    'Eliminar regla
    MyRange.FormatConditions(1).Delete
End Sub

Este código crea una nueva regla para el rango A1:A10, y luego la borra. Debes utilizar el número de índice correcto para la eliminación, así que comprueba en ‘Gestionar Reglas’ en el front-end de Excel (esto mostrará las reglas en orden de ejecución) para asegurarte de que obtienes el número de índice correcto. Ten en cuenta que no hay posibilidad de deshacer en Excel si eliminas una regla de formato condicional en VBA, a diferencia de si lo haces a través del front-end de Excel.

Modificación de una Regla

Dado que las reglas son una colección de objetos basados en un rango especificado, puede realizar fácilmente cambios en reglas concretas utilizando VBA. Las propiedades reales una vez que la regla ha sido añadida son de sólo lectura, pero puede utilizar el método Modificar para cambiarlas. Las propiedades como los colores son de lectura/escritura.

Sub CambiarFormatoCondicional()
    Dim MyRange As Range
    'Crear objeto rango
    Set MyRange = Range("A1:A10")
    'Borrar formatos condicionales previos
    MyRange.FormatConditions.Delete
    'Agregar primera regla
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=100", Formula2:="=150"
            MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
    'Cambiar Regla
    MyRange.FormatConditions(1).Modify xlCellValue, xlLess, "10"
    'Cambiar Color de la Regla
    MyRange.FormatConditions(1).Interior.Color = vbGreen
End Sub

Este código crea un objeto de rango (A1:A10) y añade una regla para números entre 100 y 150. Si la condición es verdadera entonces el color de la celda cambia a rojo.

El código entonces cambia la regla para números menores de 10. Si la condición es verdadera, el color de la celda cambia a verde.

Uso de una Combinación de Colores Degradada

El formato condicional de Excel tiene un medio para usar colores degradadps en un rango de números que van en orden ascendente o descendente.

Esto es muy útil cuando se tienen datos como cifras de ventas por área geográfica, temperaturas de ciudades o distancias entre ciudades. Usando VBA, tiene la ventaja añadida de poder elegir su propio esquema de colores graduados, en lugar de los estándar ofrecidos en el front-end de Excel.

Sub ColoresDegradados()
    Dim MyRange As Range
    'Crear objeto rango
    Set MyRange = Range("A1:A10")
    'Borrar formatos condicionales previos
    MyRange.FormatConditions.Delete
    'Definir tipo de escala
    MyRange.FormatConditions.AddColorScale ColorScaleType:=3
    'Seleccionar el color para el valor más bajo del rango
    MyRange.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With MyRange.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
    End With
    'Seleccione el color para los valores medios del rango
    MyRange.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    MyRange.FormatConditions(1).ColorScaleCriteria(2).Value = 50
   'Seleccione el color del punto medio del rango
    With MyRange.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
    End With
     'Seleccione el color para el valor más alto del rango
    MyRange.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With MyRange.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
    End With
End Sub

Cuando este código se ejecuta, graduará los colores de las celdas según los valores ascendentes del rango A1:A10.

 

Esta es una forma muy impresionante de mostrar los datos y sin duda captará la atención de los usuarios.

Formato Condicional Para Valores de Error

Cuando se tiene una gran cantidad de datos, es fácil que se pierda un valor de error en las distintas hojas de trabajo. Si esto se presenta a un usuario sin ser resuelto, podría llevar a grandes problemas y a que el usuario pierda la confianza en los números. Esto utiliza un tipo de regla de xlExpression y una función de Excel de IsError para evaluar la celda. Puede crear un código para que todas las celdas con errores tengan un color de celda rojo:

Sub FormatoCondicionalParaValoresError()
    Dim MyRange As Range
    'Crear objeto rango
    Set MyRange = Range("A1:A10")
    'Borrar formatos condicionales previos
    MyRange.FormatConditions.Delete
    'Agregar regla de error
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=IsError(A1)=true"
    'Establecer el color interior en rojo
    MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

Formato Condicional para Fechas en el Pasado

Es posible que tenga datos importados en los que quiera resaltar las fechas que están en el pasado. Un ejemplo de esto podría ser un informe de deudores en el que quiere que se destaquen las fechas de las facturas antiguas de más de 30 días. Este código utiliza el tipo de regla xlExpression y una función de Excel para evaluar las fechas.

Sub FormatoCondicionalFechasPasadas()
    Dim MyRange As Range
    'Crear Objeto Rango basado en una columna de fechas
    Set MyRange = Range("A1:A10")
    'Borrar formatos condicionales previos
    MyRange.FormatConditions.Delete
    'Añadir regla de error para fechas pasadas
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=Now()-A1 > 30"
    'Establecer el color interior en rojo
    MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

Este código tomará un rango de fechas en el rango A1:A10, y establecerá el color de la celda en rojo para cualquier fecha que sea mayor a 30 días en el pasado.

En la fórmula que se utiliza en la condición, Now() da la fecha y hora actuales. Esto seguirá recalculando cada vez que se recalcule la hoja de trabajo, por lo que el formato cambiará de un día a otro.

Uso de Barras de Datos en el Formato Condicional de VBA

Puede utilizar VBA para añadir barras de datos a un rango de números. Éstas son casi como mini gráficos, y dan una visión instantánea de cuán grandes son los números en relación con los demás. Al aceptar valores por defecto para las barras de datos, el código es muy fácil de escribir.

Sub FormatoCondicionalBarraDeDatos()
    Dim MyRange As Range
    Set MyRange = Range("A1:A10")
    MyRange.FormatConditions.Delete
    MyRange.FormatConditions.AddDatabar
End Sub

Sus datos tendrán este aspecto en la hoja de cálculo:

 

Uso de Iconos en el Formato Condicional de VBA

Puedes utilizar el formato condicional para poner iconos junto a tus números en una hoja de cálculo. Los iconos pueden ser flechas o círculos o varias otras formas. En este ejemplo, el código añade iconos de flechas a los números en función de sus valores porcentuales:

Sub FormatoCondicionalConjuntoDeIconos()
    Dim MyRange As Range
    'Crear Objeto Rango
    Set MyRange = Range("A1:A10")
    'Borrar formatos condicionales previos
    MyRange.FormatConditions.Delete
    'Añadir el conjunto de iconos al objeto FormatConditions
    MyRange.FormatConditions.AddIconSetCondition
    'Poner el conjunto de iconos en flechas - condición 1
    With MyRange.FormatConditions(1)
        .IconSet = ActiveWorkbook.IconSets(xl3Arrows)
    End With
    'Establecer el criterio del icono para el valor porcentual requerido - condición 2
    With MyRange.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValuePercent
        .Value = 33
        .Operator = xlGreaterEqual
    End With
    'Establecer el criterio del icono para el valor porcentual requerido - condición 3
    With MyRange.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValuePercent
        .Value = 67
        .Operator = xlGreaterEqual
    End With
End Sub

Esto dará una vista instantánea mostrando si un número es alto o bajo. Después de ejecutar este código, su hoja de cálculo tendrá el siguiente aspecto:

 

Uso de Formato Condicional para Resaltar los Cinco Principales

Puede utilizar el código VBA para resaltar los 5 números principales dentro de un rango de datos. Utiliza un parámetro llamado ‘AddTop10’, pero puede ajustar el número de rango dentro del código a 5. Un usuario puede desear ver los números más altos de un rango sin tener que ordenar los datos primero.

Sub FormatoCondicionalCincoPrincipales()
    Dim MyRange As Range
    'Crear Objeto Rango
    Set MyRange = Range("A1:A13")
    'Borrar formatos condicionales previos
    MyRange.FormatConditions.Delete
    'Añadir una condición Top10
    MyRange.FormatConditions.AddTop10
    With MyRange.FormatConditions(1)
        'Establecer el parámetro de arriba a abajo
        .TopBottom = xlTop10Top
        'Establecer sólo el top 5
        .Rank = 5
    End With
    With MyRange.FormatConditions(1).Font
        'Establecer el color de la fuente
        .Color = -16383844
    End With
    With MyRange.FormatConditions(1).Interior
        'Establecer el color de fondo de la celda
        .Color = 13551615
    End With
End Sub

Los datos de la hoja de cálculo tendrían este aspecto después de ejecutar el código:

 

Observe que el valor de 145 aparece dos veces.

Importancia de los Parámetros StopIfTrue y SetFirstPriority

StopIfTrue es importante si un rango de celdas tiene múltiples reglas de formato condicional. Una sola celda dentro del rango puede satisfacer la primera regla, pero también puede satisfacer las reglas posteriores. Como desarrollador, puede querer que se muestre el formato sólo para la primera regla a la que llegue. Los criterios de otras reglas pueden superponerse y pueden hacer cambios no deseados si se permite continuar hacia abajo en la lista de reglas.

El valor por defecto de este parámetro es True, pero puede cambiarlo si quiere que se consideren todas las demás reglas para esa celda:

MyRange.FormatConditions(1).StopIfTrue = False

El parámetro SetFirstPriority dicta si esa regla de condición será evaluada primero cuando hay múltiples reglas para esa celda.

MyRange.FormatConditions(1).SetFirstPriority

Esto mueve la posición de esa regla a la posición 1 dentro de la colección de condiciones de formato, y cualquier otra regla se moverá hacia abajo con los números de índice cambiados. Tenga cuidado si hace algún cambio en las reglas en el código utilizando los números de índice. Debe asegurarse de que está cambiando o eliminando la regla correcta.

Puede cambiar la prioridad de una regla:

MyRange.FormatConditions(1).Priority=3

Esto cambiará las posiciones relativas de cualquier otra regla dentro de la lista de formato condicional.

Uso del Formato Condicional con Referencia a Otros Valores de Celda

Esto es algo que el formato condicional de Excel no puede hacer. Sin embargo, usted puede construir su propio código VBA para hacer esto.

Suponga que tiene una columna de datos, y en la celda adyacente a cada número, hay algún texto que indica qué formato debe tener lugar en cada número.

El siguiente código recorrerá su lista de números, buscará en la celda adyacente el texto de formato, y luego formateará el número como sea necesario:

Sub FormatoCondicionalReferencia_a_OtraCelda()
    'Crear variables para mantener el número de filas de los datos tabulares
    Dim RRow As Long, N As Long
    'Capturar el número de filas dentro del rango de datos tabulares
    RRow = ActiveSheet.UsedRange.Rows.Count
    'Iterar a través de todas las filas del rango de datos tabulares
    For N = 1 To RRow
        'Utilice una sentencia Select Case para evaluar el formato basado en la columna 2
        Select Case ActiveSheet.Cells(N, 2).Value
            'Cambia el color interior a azul
            Case "Azul"
            ActiveSheet.Cells(N, 1).Interior.Color = vbBlue
            'Cambia el color interior a rojo
            Case "Rojo"
            ActiveSheet.Cells(N, 1).Interior.Color = vbRed
            'Cambia el color interior a verde
            Case "Verde"
            ActiveSheet.Cells(N, 1).Interior.Color = vbGreen
        End Select
    Next N
End Sub

Una vez ejecutado este código, su hoja de cálculo tendrá el siguiente aspecto:

Las celdas a las que se refiere el formato pueden estar en cualquier parte de la hoja de trabajo, o incluso en otra hoja de trabajo dentro del libro. Puedes usar cualquier forma de texto para hacer una condición para el formato, y sólo estás limitado por tu imaginación en los usos que puedes darle a este código.

Operadores Que Se Pueden Utilizar en las Sentencias de Formato Condicional

Como ha visto en los ejemplos anteriores, los operadores se utilizan para determinar cómo se evaluarán los valores de la condición, por ejemplo, xlBetween.

Hay una serie de estos operadores que se pueden utilizar, dependiendo de cómo se desea especificar los criterios de la regla.

Nombre	Valor	Descripción
xlBetween	1	Entre. Sólo puede utilizarse si se proporcionan dos fórmulas.
xlEqual	3	Igual.
xlGreater	5	Mayor que.
xlGreaterEqual	7	Mayor o igual que.
xlLess	6	Menor que.
xlLessEqual	8	Menor o igual que.
xlNotBetween	2	No entre. Sólo puede utilizarse si se proporcionan dos fórmulas.
xlNotEqual	4	No es igual a.