Formato condicional de Excel
El formato condicional de Excel le permite definir reglas que determinan el formato de celda.
Por ejemplo, puede crear una regla que resalte las celdas que cumplan con ciertos criterios. Ejemplos incluyen:
- Números que caen dentro de un cierto rango (por ejemplo, menos de 0).
- Los 10 elementos principales 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 puede encontrar 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 mediante 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á editarlos o eliminarlos a menos que 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 en particular y a un rango particular de celdas. Si se necesitan en otra parte del libro de trabajo, también deben configurarse en esa hoja de trabajo.
Usos prácticos del formato condicional en VBA
Es posible que tenga una gran cantidad de datos sin procesar importados a su hoja de trabajo desde un archivo CSV (valores separados por comas) o desde una tabla de base de datos o consulta. Esto puede fluir a través de un panel 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, p. Ej. color de fondo de la celda en rojo, y puede hacer esto configurando el formato condicional. De esta manera, el usuario será atraído instantáneamente a este número y luego podrá investigar por qué sucede esto.
Puede usar VBA para activar o desactivar el formato condicional. Puede usar VBA para borrar las reglas en un rango de celdas o volver a activarlas. Puede haber una situación en la que exista una razón perfectamente válida para un número inusual, pero cuando el usuario presenta el panel de control o informa a un nivel superior de administración, desea poder eliminar las "campanas de alarma".
Además, en los datos importados sin procesar, es posible que desee resaltar dónde los números 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 usar VBA para evaluar el tamaño del nuevo rango de datos e insertar formato condicional solo para ese rango.
También puede tener una situación en la que haya una lista ordenada de nombres con valores numéricos para cada uno, p. Ej. salario de empleado, notas de examen. Con el formato condicional, puede usar colores graduados para ir de mayor a menor, lo que se ve muy impresionante para fines de presentación.
Sin embargo, la lista de nombres no siempre tendrá un tamaño estático, y puede usar el código VBA para actualizar la escala de colores graduados de acuerdo con los cambios en el tamaño del rango.
Un ejemplo simple de creación de un formato condicional en un rango
Este ejemplo configura el formato condicional para un rango de celdas (A1: A10) en una hoja de trabajo. Si el número en el rango está entre 100 y 150, el color de fondo de la celda será rojo; de lo contrario, no tendrá color.
| 1234567891011121314 | Sub ConditionalFormattingExample ()"Definir rangoAtenuar MyRange como rangoEstablecer MyRange = Range ("A1: A10")‘Eliminar el formato condicional existente del rangoMyRange.FormatConditions.Delete"Aplicar formato condicionalMyRange.FormatConditions.Add Tipo: = xlCellValue, Operador: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Observe que primero definimos el rango MyRange para aplicar formato condicional.
A continuación, eliminamos cualquier formato condicional existente para el rango. Esta es una buena idea para evitar que se agregue la misma regla cada vez que se ejecuta el código (por supuesto, no será apropiado en todas las circunstancias).
Los colores se dan mediante valores numéricos. Es una buena idea utilizar la notación RGB (rojo, verde, azul) para esto. Puede utilizar constantes de color estándar para esto, p. Ej. vbRed, vbBlue, pero está limitado a ocho opciones de color.
Hay más de 16,7 millones de colores disponibles y, con RGB, puede acceder a todos ellos. Esto es mucho más fácil que tratar de recordar qué número va con qué color. Cada uno de los tres números de color RGB es 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 cumplirán la condición.
Formato multicondicional
Es posible que desee configurar varias reglas condicionales dentro de su rango de datos para que todos los valores en un rango estén cubiertos por diferentes condiciones:
| 12345678910111213141516171819 | Sub MultipleConditionalFormattingExample ()Atenuar MyRange como rango'Crear objeto de rangoEstablecer MyRange = Rango ("A1: A10")'Eliminar formatos condicionales anterioresMyRange.FormatConditions.Delete'Agregar primera reglaMyRange.FormatConditions.Add Tipo: = xlCellValue, Operador: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Agregar segunda reglaMyRange.FormatConditions.Add Tipo: = xlCellValue, Operador: = xlLess, _Fórmula1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Agregar tercera reglaMyRange.FormatConditions.Add Tipo: = xlCellValue, Operador: = xlGreater, _Fórmula1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbYellowEnd Sub |
Este ejemplo establece la primera regla como antes, con el color de celda rojo si el valor de la celda está entre 100 y 150.
Luego se agregan 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, debe asegurarse de que se cubran todas las posibilidades de números y que las reglas no se superpongan.
Si las celdas en blanco están en este rango, se mostrarán en azul, porque Excel todavía las considera con un valor menor que 100.
La forma de evitar esto es agregar otra condición como expresión. Esto debe agregarse como la primera regla de condición dentro del código. Cuando hay varias reglas, es muy importante obtener el orden de ejecución correcto; de lo contrario, los resultados pueden ser impredecibles.
| 1234567891011121314151617181920212223 | Sub MultipleConditionalFormattingExample ()Atenuar MyRange como rango'Crear objeto de rangoEstablecer MyRange = Range ("A1: A10")'Eliminar formatos condicionales anterioresMyRange.FormatConditions.Delete'Agregar primera reglaMyRange.FormatConditions.Agregar tipo: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Agregar segunda reglaMyRange.FormatConditions.Add Tipo: = xlCellValue, Operador: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Agregar tercera reglaMyRange.FormatConditions.Add Tipo: = xlCellValue, Operador: = xlLess, _Fórmula1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue'Agregar cuarta reglaMyRange.FormatConditions.Add Tipo: = xlCellValue, Operador: = xlGreater, _Fórmula1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)End Sub |
Esto usa el tipo de xlExpression y luego usa 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 que comienza en 1. Puede iterar a través de este objeto usando un bucle For… Next o For… Each.
Eliminar una regla
A veces, es posible que deba eliminar una regla individual en un conjunto de reglas múltiples si no se ajusta a los requisitos de datos.
| 12345678910111213 | Sub DeleteConditionalFormattingExample ()Atenuar MyRange como rango'Crear objeto de rangoEstablecer MyRange = Range ("A1: A10")'Eliminar formatos condicionales anterioresMyRange.FormatConditions.Delete'Agregar primera reglaMyRange.FormatConditions.Add Tipo: = xlCellValue, Operador: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Eliminar reglaMyRange.FormatConditions (1) .DeleteEnd Sub |
Este código crea una nueva regla para el rango A1: A10 y luego la elimina. Debe usar el número de índice correcto para la eliminación, por lo tanto, marque "Administrar reglas" en la interfaz de Excel (esto mostrará las reglas en orden de ejecución) para asegurarse de obtener el número de índice correcto. Tenga en cuenta que no hay una función de deshacer en Excel si elimina una regla de formato condicional en VBA, a diferencia de si lo hace a través del front-end de Excel.
Cambiar una regla
Debido a que las reglas son una colección de objetos basados en un rango específico, puede realizar cambios fácilmente en reglas particulares usando VBA. Las propiedades reales una vez que se ha agregado la regla son de solo lectura, pero puede usar el método Modify para cambiarlas. Las propiedades como los colores son de lectura / escritura.
| 123456789101112131415 | Sub ChangeConditionalFormattingExample ()Atenuar MyRange como rango'Crear objeto de rangoEstablecer MyRange = Rango ("A1: A10")'Eliminar formatos condicionales anterioresMyRange.FormatConditions.Delete'Agregar primera reglaMyRange.FormatConditions.Add Tipo: = xlCellValue, Operador: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Cambiar reglaMyRange.FormatConditions (1) .Modify xlCellValue, xlLess, "10""Cambiar el color de la reglaMyRange.FormatConditions (1) .Interior.Color = vbGreenEnd Sub |
Este código crea un objeto de rango (A1: A10) y agrega una regla para números entre 100 y 150. Si la condición es verdadera, el color de la celda cambia a rojo.
Luego, el código cambia la regla a números menores que 10. Si la condición es verdadera, el color de la celda ahora cambia a verde.
Usar un esquema de color graduado
El formato condicional de Excel tiene un medio de usar colores graduados en un rango de números que se ejecutan en orden ascendente o descendente.
Esto es muy útil cuando tiene datos como cifras de ventas por área geográfica, temperaturas de la ciudad o distancias entre ciudades. Al usar VBA, tiene la ventaja adicional de poder elegir su propio esquema de color graduado, en lugar de los estándar que se ofrecen en el front-end de Excel.
| 1234567891011121314151617181920212223242526272829 | Colores subgraduados ()Atenuar MyRange como rango'Crear objeto de rangoEstablecer MyRange = Rango ("A1: A10")'Eliminar formatos condicionales anterioresMyRange.FormatConditions.Delete'Definir tipo de escalaMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Seleccione el color para el valor más bajo en el rangoMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueCon MyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Color = 7039480Terminar con'Seleccione el color para los valores medios en el rangoMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Value = 50'Seleccione el color para el punto medio del rangoCon MyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Color = 8711167Terminar con'Seleccione el color para el valor más alto en el rangoMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueCon MyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Color = 8109667Terminar conEnd Sub |
Cuando se ejecuta este código, graduará los colores de la celda de acuerdo con los valores ascendentes en el rango A1: A10.
Esta es una forma muy impresionante de mostrar los datos y sin duda llamará la atención de los usuarios.
Formato condicional para valores de error
Cuando tiene una gran cantidad de datos, puede perder fácilmente un valor de error en sus diversas hojas de trabajo. Si esto se presenta a un usuario sin ser resuelto, podría ocasionar grandes problemas y el usuario podría perder la confianza en los números. Esto usa un tipo de regla de xlExpression y una función de Excel de IsError para evaluar la celda.
Puede crear código para que todas las celdas con errores tengan un color de celda rojo:
| 1234567891011 | Sub ErrorConditionalFormattingExample ()Atenuar MyRange como rango'Crear objeto de rangoEstablecer MyRange = Rango ("A1: A10")'Eliminar formatos condicionales anterioresMyRange.FormatConditions.Delete'Agregar regla de errorMyRange.FormatConditions.Add Tipo: = xlExpression, Formula1: = "= IsError (A1) = true"'Establecer el color interior en rojoMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Formato condicional para fechas en el pasado
Es posible que tenga datos importados donde desee resaltar fechas que están en el pasado. Un ejemplo de esto podría ser un informe de deudores en el que desea que se destaque cualquier fecha de factura anterior con más de 30 días de antigüedad.
Este código usa el tipo de regla de xlExpression y una función de Excel para evaluar las fechas.
| 1234567891011 | Sub DateInPastConditionalFormattingExample ()Atenuar MyRange como rango'Crear objeto de rango basado en una columna de fechasEstablecer MyRange = Range ("A1: A10")'Eliminar formatos condicionales anterioresMyRange.FormatConditions.Delete'Agregar regla de error para fechas pasadasMyRange.FormatConditions.Agregar tipo: = xlExpression, Formula1: = "= Now () - A1> 30"'Establecer el color interior en rojoMyRange.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 tenga más de 30 días en el pasado.
En la fórmula que se usa en la condición, Now () da la fecha y hora actuales. Esto seguirá recalculando cada vez que se recalcula la hoja de trabajo, por lo que el formato cambiará de un día para el siguiente.
Uso de barras de datos en formato condicional de VBA
Puede usar VBA para agregar barras de datos a un rango de números. Estos son casi como mini gráficos y brindan una vista instantánea de qué tan grandes son los números entre sí. Al aceptar valores predeterminados para las barras de datos, el código es muy fácil de escribir.
| 123456 | Sub DataBarFormattingExample ()Atenuar MyRange como rangoEstablecer MyRange = Range ("A1: A10")MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarEnd Sub |
Sus datos se verán así en la hoja de trabajo:
Uso de iconos en formato condicional de VBA
Puede utilizar el formato condicional para colocar iconos junto a sus números en una hoja de trabajo. Los iconos pueden ser flechas o círculos o varias otras formas. En este ejemplo, el código agrega íconos de flecha a los números según sus valores porcentuales:
| 12345678910111213141516171819202122232425 | Sub IconSetsExample ()Atenuar MyRange como rango'Crear objeto de rangoEstablecer MyRange = Rango ("A1: A10")'Eliminar formatos condicionales anterioresMyRange.FormatConditions.Delete'Agregar conjunto de iconos al objeto FormatConditionsMyRange.FormatConditions.AddIconSetCondition'Establezca el conjunto de iconos en flechas - condición 1Con MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Terminar con'establecer el criterio del icono para el valor porcentual requerido - condición 2Con MyRange.FormatConditions (1) .IconCriteria (2).Tipo = xlConditionValuePercent.Valor = 33.Operator = xlGreaterEqualTerminar con'establecer el criterio del icono para el valor porcentual requerido - condición 3Con MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercent.Valor = 67.Operador = xlGreaterEqualTerminar conEnd Sub |
Esto le dará una vista instantánea que muestra si un número es alto o bajo. Después de ejecutar este código, su hoja de trabajo se verá así:
Uso de formato condicional para resaltar los cinco primeros
Puede usar 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 en un rango sin tener que ordenar los datos primero.
| 1234567891011121314151617181920212223 | Sub Top5Ejemplo ()Atenuar MyRange como rango'Crear objeto de rangoEstablecer MyRange = Range ("A1: A10")'Eliminar formatos condicionales anterioresMyRange.FormatConditions.Delete'Agregar una condición Top10MyRange.FormatConditions.AddTop10Con MyRange.FormatConditions (1)'Establecer parámetro de arriba a abajo.TopBottom = xlTop10Top'Set top 5 solamente.Rango = 5Terminar conCon MyRange.FormatConditions (1) .Font'Establecer el color de la fuente.Color = -16383844Terminar conCon MyRange.FormatConditions (1) .Interior'Establecer el color de fondo de la celda.Color = 13551615Terminar conEnd Sub |
Los datos en su hoja de trabajo se verían así después de ejecutar el código:
Tenga en cuenta que el valor de 145 aparece dos veces, por lo que se resaltan seis celdas.
Importancia de los parámetros StopIfTrue y SetFirstPriority
StopIfTrue es importante si un rango de celdas tiene varias 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, es posible que desee que muestre el formato solo para la primera regla a la que se refiere. Otros criterios de reglas pueden superponerse y pueden realizar cambios no deseados si se les permite continuar hacia abajo en la lista de reglas.
El valor predeterminado de este parámetro es Verdadero, pero puede cambiarlo si desea que se consideren todas las demás reglas para esa celda:
| 1 | MyRange. FormatConditions (1) .StopIfTrue = False |
El parámetro SetFirstPriority dicta si esa regla de condición se evaluará primero cuando haya varias reglas para esa celda.
| 1 | 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 está realizando cambios en las reglas del código utilizando los números de índice. Debe asegurarse de cambiar o eliminar la regla correcta.
Puede cambiar la prioridad de una regla:
| 1 | MyRange. FormatConditions (1) .Priority = 3 |
Esto cambiará las posiciones relativas de cualquier otra regla dentro de la lista de formato condicional.
Uso de formato condicional que hace referencia a otros valores de celda
Esto es algo que el formato condicional de Excel no puede hacer. Sin embargo, puede crear 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 un texto que indica qué formato debe tener lugar en cada número.
El siguiente código recorrerá su lista de números, busque en la celda adyacente para formatear el texto y luego formatee el número según sea necesario:
| 123456789101112131415161718192021 | Sub ReferToAnotherCellForConditionalFormatting ()'Cree variables para contener el número de filas para los datos tabularesAtenuar RRow tan largo, N tan largo'Capture el número de filas dentro del rango de datos tabularesRRow = ActiveSheet.UsedRange.Rows.Count'Iterar a través de todas las filas en el rango de datos tabularesPara N = 1 a Row'Use una declaración Select Case para evaluar el formato según la columna 2Seleccione Case ActiveSheet.Cells (N, 2) .Value'Convierte el color interior en azulCaso "azul"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue'Cambia el color interior a rojoCaso "rojo"ActiveSheet.Cells (N, 1) .Interior.Color = vbRed'Cambia el color del interior a verdeCaso "verde"ActiveSheet.Cells (N, 1) .Interior.Color = vbGreenFinalizar SeleccionarSiguiente NEnd Sub |
Una vez que se haya ejecutado este código, su hoja de trabajo se verá así:
Las celdas a las que se hace referencia para el formato pueden estar en cualquier lugar de la hoja de trabajo, o incluso en otra hoja de trabajo dentro del libro de trabajo. Puede usar cualquier forma de texto para establecer una condición para el formato, y solo está limitado por su imaginación en los usos que podría darle a este código.
Operadores que se pueden usar en declaraciones 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, p. Ej. xlBetween.
Hay varios de estos operadores que se pueden utilizar, dependiendo de cómo desee especificar los criterios de su regla.
| Nombre | Valor | Descripción |
| xlEntre | 1 | Entre. Solo se puede usar si se proporcionan dos fórmulas. |
| xlEqual | 3 | Igual. |
| xlGreater | 5 | Mas grande que. |
| xlGreaterEqual | 7 | Mayor qué o igual a. |
| xlmenos | 6 | Menos que. |
| xlLessEqual | 8 | Menos que o igual a. |
| xlNotBetween | 2 | No entre. Solo se puede usar si se proporcionan dos fórmulas. |
| xlNotEqual | 4 | No es igual. |
