VBA Formato condicional en Excel — reglas que siguen vivas, no un bucle que se queda obsoleto

TL;DR — Una regla de formato condicional es una instrucción permanente que le das a Excel una sola vez; Excel la vuelve a ejecutar en cada edición. Un bucle For Each ... Interior.Color es una fotografía — correcta en el instante en que se ejecuta, obsoleta en el instante en que cambia un valor. Añades reglas a través de la colección FormatConditions de un rango. Dos cosas hacen tropezar a todo el mundo: las reglas se acumulan si no haces Delete antes de Add, y en una regla de expresión la referencia tiene que ser $D2 (columna bloqueada, fila libre) o resalta las celdas equivocadas.

Dim rng As Range: Set rng = ThisWorkbook.Worksheets("Sales").Range("A2:F1000")
rng.FormatConditions.Delete                              ' limpia primero las reglas viejas (idempotente)
With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$D2>1000")
    .Interior.Color = RGB(198, 239, 206)                 ' toda la fila se pone verde cuando la columna D > 1000
End With

Colorear una celda desde VBA tiene dos modos completamente distintos, y elegir el equivocado es un bug silencioso. Puedes fijar Interior.Color en un bucle — un pintado puntual — o puedes añadir una regla y dejar que Excel se quede al cargo del coloreado para siempre. Esta guía va del segundo, porque es el que sigue siendo correcto cuando los datos cambian, y aquel en cuyo lugar la gente echa mano de un bucle frágil.

Lo que aprenderás

• El modelo mental — una regla es un espejo vivo, un bucle es una fotografía
• Añadir una regla a través de la colección FormatConditions
• El hábito que la mantiene idempotente — Delete antes de Add
• Los dos tipos de regla que de verdad usas — xlCellValue y xlExpression
• La trampa de la referencia — por qué resaltar la fila entera necesita $D2, no D2 ni $D$2
• Cuándo un bucle estático sigue siendo la herramienta correcta

El modelo mental: un espejo vivo, no una fotografía

Resaltar las filas vencidas con un bucle produce una instantánea. Es correcta en el momento en que se ejecuta y se equivoca en el momento en que alguien edita una fecha de vencimiento — el color no se mueve, porque nada vuelve a ejecutar el bucle:

' Instantanea: correcta ahora, obsoleta tras la siguiente edicion.
Dim c As Range
For Each c In ws.Range("D2:D1000")
    If c.Value > 1000 Then c.EntireRow.Interior.Color = RGB(198, 239, 206)
Next c

Una regla de formato condicional es distinta en su naturaleza. Describes la condición una vez, se la das a Excel, y Excel la vuelve a evaluar en cada recálculo, para siempre. Cambia un valor y el color lo sigue en el mismo instante. Esa es toda la razón por la que el formato condicional existe en código: un bucle pinta, una regla promete. En cuanto ves el coloreado como «¿quién se queda al cargo de mantener esto correcto — yo o Excel?», la elección entre los dos modos deja de ser una cuestión de estilo y pasa a ser una de corrección.

Añadir una regla: la colección FormatConditions

Todo rango lleva una colección FormatConditions. Le haces Add a una condición; Add devuelve la condición nueva, cuyos .Interior, .Font y .Borders fijas después:

Dim rng As Range: Set rng = ws.Range("B2:B1000")
With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="0")
    .Interior.Color = RGB(255, 199, 206)   ' relleno rojo cuando el valor es negativo
    .Font.Color = RGB(156, 0, 6)
End With

La colección vive en el rango al que se aplica la regla — B2:B1000 aquí — así que la regla y su alcance se fijan de una misma vez. .Add toma el Type de la regla y (para una regla de valor) un Operator y uno o dos umbrales Formula1/Formula2. Todo lo que viene después no es más que dar formato al objeto de condición que te ha devuelto.

El hábito que más importa: Delete antes de Add

Este es el modo de fallo que tarde o temprano pilla a toda macro de formato condicional. .Add no reemplaza — añade. Ejecuta la macro dos veces y el rango tiene dos reglas idénticas; ejecútala en un bucle sobre hojas y acabas con cientos, cada una un pequeño coste de rendimiento y una pesadilla de desenredar a mano:

rng.FormatConditions.Delete                 ' <- la unica linea que mantiene esto idempotente
With rng.FormatConditions.Add(...)          ' ahora hay exactamente una regla, en cada ejecucion
    ...
End With

Haz que Delete antes de Add te salga por reflejo. Es la diferencia entre una macro que puedes ejecutar mil veces con el mismo resultado y una que acumula porquería en silencio. Si tienes que conservar reglas que el usuario añadió a mano, borra con más cirugía recorriendo FormatConditions y quitando solo las tuyas — pero para una macro que es dueña del formato del rango, un Delete limpio primero es el valor por defecto honesto.

Los dos tipos de regla que de verdad usas

Hay varios valores de Type, pero dos cargan con casi todo el trabajo real:

• xlCellValue — compara el valor de esta celda. Operator:=xlLess, xlGreater, xlBetween, xlEqual. Este es el caso simple de «colorea la celda por su propio número».
• xlExpression — evalúa una fórmula que devuelve TRUE/FALSE. Este es el potente: puede referenciar otras columnas, que es como coloreas una fila entera a partir de un solo campo.

' Regla de valor: pinta una celda de rojo cuando su propio valor es menor que cero.
rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"

' Regla de expresion: pinta la fila ENTERA cuando la columna D supera 1000.
ws.Range("A2:F1000").FormatConditions.Add Type:=xlExpression, Formula1:="=$D2>1000"

Los extras — AddDatabar, AddColorScale, AddIconSetCondition — son la misma colección con visuales más ricos, pero si entiendes valor frente a expresión, entiendes el modelo.

La trampa de la referencia: por qué resaltar la fila entera necesita $D2

Este es el bug número uno de «mi regla resalta las celdas equivocadas», y es pura mecánica de hoja de cálculo. Una fórmula xlExpression se evalúa respecto a la celda superior izquierda del rango aplicado, y luego Excel la recorre por cada celda igual que arrastraría una fórmula. Así que los símbolos de dólar deciden qué se mueve:

• =$D2>1000 — columna bloqueada, fila libre. Cada celda de una fila dada prueba la columna D de esa fila, así que la fila entera se enciende junta. Esto es lo que quieres para resaltar filas.
• =D2>1000 — nada bloqueado. La referencia se desplaza también por columna, así que la fila 2 prueba D2, pero la columna B de la fila 2 prueba E2, y todo se emborrona.
• =$D$2>1000 — todo bloqueado. Cada celda de todo el rango prueba la única celda D2 — así que el bloque entero está encendido o apagado a la vez.

Equivoca el anclaje y la regla «funciona» (sin error) pero colorea disparates. El arreglo es pensarlo exactamente como pensarías una fórmula arrastrada: bloquea la columna que estás probando, deja la fila relativa.

Cuándo un bucle estático sigue siendo lo correcto

Las reglas no siempre son la respuesta. Usa un bucle Interior.Color normal cuando el color no deba seguir los datos — un informe puntual que estás a punto de congelar y exportar como PDF, donde quieres el resaltado horneado e inmutable incluso después de que alguien edite una celda. En ese caso una regla viva es la herramienta equivocada: seguiría recoloreando un documento que se supone que es una instantánea fija.

El criterio es una línea limpia: si el color debe seguir siendo correcto a medida que se edita la hoja, usa una regla; si el color debe congelarse tal como está ahora, usa un bucle. La mayoría de las veces quieres la regla — que es justo por lo que echar mano del bucle por reflejo es el error que vale la pena desaprender.

Cómo ayuda ExcelMaster

El formato condicional en código tiene tres trampas silenciosas: reglas que se acumulan porque nada las borra primero, una regla de expresión anclada $D$2 o D2 en lugar de $D2 de modo que colorea las celdas equivocadas, y un bucle frágil usado donde correspondía una regla, así que el resaltado se queda obsoleto en la siguiente edición. Cada una se ejecuta sin error y se ve mal más tarde.

ExcelMaster te deja describir el resultado — «resalta las filas donde el total supere 1000», «sombrea de rojo los negativos», «marca los elementos vencidos y mantenlo vivo». Escribe un FormatConditions.Delete antes de Add para que la macro sea idempotente, ancla las reglas de expresión como $D2 para resaltar bien la fila entera, y solo baja a un bucle estático cuando de verdad quieres un informe congelado. Te quedas con el libro y con el código — y el color sigue los datos tal como querías.

Preguntas frecuentes

¿Cómo añado formato condicional en VBA?

Añade una regla a la colección FormatConditions de un rango: Range("B2:B1000").FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0", y luego fija el formato de la condición devuelta (.Interior.Color, .Font.Color). La regla vive en el rango al que se aplica, y Excel la vuelve a evaluar automáticamente cada vez que los datos cambian.

¿Por qué mis reglas de formato condicional se multiplican?

Porque FormatConditions.Add añade en lugar de reemplazar, así que cada ejecución agrega otra copia. Llama a Range(...).FormatConditions.Delete antes de Add para mantener la macro idempotente — exactamente una regla, sin importar cuántas veces se ejecute. Para limpiar la hoja entera, usa Cells.FormatConditions.Delete.

¿Cómo resalto una fila entera con formato condicional en VBA?

Usa una regla de expresión sobre el rango de la fila entera y bloquea la columna probada, no la fila: Range("A2:F1000").FormatConditions.Add Type:=xlExpression, Formula1:="=$D2>1000". La referencia $D2 (columna bloqueada, fila relativa) hace que cada fila pruebe su propia columna D, así que la fila completa se colorea junta. $D$2 o D2 resaltarán las celdas equivocadas.

¿Cuál es la diferencia entre xlCellValue y xlExpression?

xlCellValue compara cada celda contra un umbral con un Operator (xlLess, xlGreater, xlBetween) — bien para colorear una celda por su propio valor. xlExpression evalúa una fórmula que devuelve TRUE/FALSE y puede referenciar otras columnas, que es como coloreas una fila entera a partir de un solo campo. Usa reglas de expresión para cualquier cosa que cruce columnas.

¿Debo usar formato condicional o un bucle de VBA para colorear celdas?

Usa una regla cuando el color deba seguir siendo correcto a medida que se edita la hoja — Excel vuelve a evaluar una regla FormatConditions en cada cambio. Usa un bucle For Each ... Interior.Color solo para un informe puntual y estático que pretendes congelar y exportar, donde quieres el resaltado horneado y no reaplicado tras ediciones posteriores.

Probado en

Probado en: Excel 365 (Windows 11), VBA 7.1 — última verificación el 08/09/2026.

Guías relacionadas: VBA Color de celda · VBA ColorIndex · VBA RGB · VBA Font · VBA For Each