Formato condicional de VBA

Formato condicional en Excel VBA

Podemos aplicar formato condicional a una celda o rango de celdas en Excel. Un formato condicional es un formato que se aplica solo a las celdas que cumplen con ciertos criterios, digamos valores por encima de un valor particular, valores positivos o negativos, o valores con una fórmula en particular, etc. Este formato condicional también se puede hacer en la programación de Excel VBA usando la ' Colección de condiciones de formato ' en la macro / procedimiento.

La condición de formato se utiliza para representar un formato condicional que se puede establecer llamando a un método que devuelve una variable de ese tipo. Contiene todos los formatos condicionales para un solo rango y solo puede contener tres condiciones de formato.

FormatConditions.Add / Modify / Delete se usa en VBA para agregar / modificar / eliminar objetos FormatCondition a la colección. Cada formato está representado por un objeto FormatCondition. FormatConditions es una propiedad del objeto Range y Add tiene los siguientes parámetros con la siguiente sintaxis:

FormatConditions.Add (Tipo, Operador, Fórmula1, Fórmula2)

La sintaxis de la fórmula Add tiene los siguientes argumentos:

  • Tipo: obligatorio, representa si el formato condicional se basa en el valor presente en la celda o en una expresión
  • Operador: Opcional, representa el operador que se utilizará con un valor cuando 'Tipo' se basa en el valor de la celda
  • Fórmula1: Opcional, representa el valor o la expresión asociada con el formato condicional.
  • Fórmula2: opcional, representa el valor o la expresión asociada con la segunda parte del formato condicional cuando el parámetro: 'Operador' es 'xlBetween' o 'xlNotBetween'

FormatConditions.Modify también tiene la misma sintaxis que FormatConditions.Add.

A continuación se muestra la lista de algunos valores / enumeración que pueden tomar algunos parámetros de 'Agregar' / 'Modificar':

Ejemplos de formato condicional de VBA

A continuación se muestran los ejemplos de formato condicional en excel vba.

Puede descargar esta plantilla de formato condicional de VBA aquí - Plantilla de formato condicional de VBA

Ejemplo 1

Digamos que tenemos un archivo de Excel que contiene el nombre y las marcas de algunos estudiantes, y deseamos determinar / resaltar las marcas como Negrita y de color azul que es mayor que 80, y como Negrita y Rojo en color que es menos de 50. Veamos los datos contenidos en el archivo:

Usamos la función FormatConditions.Add como se muestra a continuación para lograr esto:

  • Vaya a Desarrollador -> Editor de Visual Basic:

  • Haga clic derecho en el nombre del libro en el panel 'Proyecto-VBAProject'->' Insertar '->' Módulo '.

  • Ahora escriba el código / procedimiento en este módulo:

Código:

 Sub formateo () End Sub

  • Defina la variable rng, condition1, condition2:

Código:

 Sub formatting () Dim rng As Range Dim condition1 Como FormatCondition, condition2 Como FormatCondition End Sub

  • Establezca / fije el rango en el que se desea el formato condicional utilizando la función 'Rango' de VBA:

Código:

 Sub formatting () Dim rng As Range Dim condition1 Como FormatCondition, condition2 Como FormatCondition Establecer rng = Range ("B2", "B11") End Sub

  • Elimine / borre cualquier formato condicional existente (si lo hubiera) del rango, usando 'FormatConditions.Delete':

Código:

 Sub formatting () Dim rng As Range Dim condition1 Como FormatCondition, condition2 Como FormatCondition Establecer rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub

  • Ahora defina y establezca los criterios para cada formato condicional, usando 'FormatConditions.Add':

Código:

 Sub formatting () Dim rng As Range Dim condition1 Como FormatCondition, condition2 Como FormatCondition Establecer rng = Range ("B2", "B11") rng.FormatConditions.Delete Establecer condición1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Establecer condición2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub

  • Definir y establecer el formato a aplicar para cada condición.

Copie y pegue este código en su módulo de clase VBA.

Código:

Sub formatting () 'Definiendo las variables: Dim rng As Range Dim condition1 Como FormatCondition, condition2 Como FormatCondition' Fijación / Configuración del rango en el que se desea el formato condicional Establecer rng = Range ("B2", "B11") 'Para eliminar / borrar cualquier formato condicional existente del rango rng.FormatConditions.Delete 'Definir y establecer los criterios para cada formato condicional Establecer condición1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Establecer condición2 = rng.FormatConditions. Add (xlCellValue, xlLess, "= 50") 'Definir y configurar el formato que se aplicará para cada condición Con condition1 .Font.Color = vbBlue .Font.Bold = True End With condition2 .Font.Color = vbRed .Font. Negrita = True End With End Sub

Ahora, cuando ejecutamos este código usando la tecla F5 o manualmente, vemos que las marcas que son menores a 50 se resaltan en negrita y rojo, mientras que aquellas que son mayores a 80 se resaltan en negrita y azul de la siguiente manera:

Nota: Algunas de las propiedades para la apariencia de celdas formateadas que se pueden usar con FormatCondition son:

Ejemplo # 2

Digamos que en el ejemplo anterior tenemos otra columna que también establece que el estudiante es un 'Topper' si obtiene más de 80 puntos, de lo contrario, se escribe Pasa / No pasa en su contra. Ahora deseamos resaltar los valores indicados como 'Topper' como Negrita y Azul. Veamos los datos contenidos en el archivo:

En este caso, el código / procedimiento funcionaría de la siguiente manera:

Código:

 Sub TextFormatting () End Sub

Definir y establecer el formato a aplicar para cada condición.

Código:

 Sub TextFormatting() With Range("c2:c11").FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="topper") With .Font .Bold = True .Color = vbBlue End With End With End Sub

We can see in the above code that we wish to test if the range: ‘C2:C11” contains the string: “Topper”, so the parameter: “Operator” of ‘Format.Add’ takes the enumeration:”xlContains”, to test this condition in the fixed range (i.e C2:C11), and then do the required conditional formatting (font changes) on this range.

Now when we run this code manually or by pressing the F5 key, we see that cell values with ‘Topper’ get highlighted in Blue and bold:

Note: So, we have seen in the above two examples how the ‘Add’ method works in case of any cell value criteria (numeric or text string).

Below are some other instances/criteria that can be used to test and thus apply VBA conditional formatting on:

  • Format by Time Period
  • Average condition
  • Colour Scale condition
  • IconSet condition
  • Databar condition
  • Unique Values
  • Duplicate Values
  • Top10 values
  • Percentile Condition
  • Blanks Condition, etc.

With different conditions to be tested, different values/enumeration are taken by parameters of ‘Add’.

Things to Remember About VBA Conditional Formatting

  • ‘Add’ method with ‘FormatConditions’ is used to create a new conditional format, ’Delete’ method to delete any conditional format, and ‘Modify’ method to alter any existing conditional format.
  • The ‘Add’ method with ‘FormatConditions Collection’ fails if more than three conditional formats are created for a single range.
  • To apply more than three conditional formats to a range using the ‘Add’ method, we can use ‘If’ or ‘select case’.
  • If the ‘Add’ method has its ‘Type’ parameter as: ‘xlExpression’, then the parameter ‘Operator’ is ignored.
  • The parameters: ‘Formula1’ and ‘Formula2’ in the ‘Add’ method can be a cell reference, constant value, string value, or even a formula.
  • The parameter: ‘Formula2’ is used only when the parameter: ‘Operator’ is either ‘xlBetween’ or ‘xlNotBetween’, else it is ignored.
  • To remove all the conditional formatting from any worksheet, we can use the ‘Delete’ method as follows:
Cells.FormatConditions.Delete