El formato condicional se puede establecer incorrectamente cuando se usa VBA en Excel

Síntomas

Imagina la siguiente situación:

• Seleccione una celda de una hoja de cálculo en Microsoft Excel 2000 o en una versión posterior de Excel.
• Use Microsoft Visual Basic para Aplicaciones (VBA) para crear un programa de formato condicional basado en fórmulas.
• Las referencias de celda relativas se usan en el programa de formato condicional vba.
• El formato condicional se aplica a una celda distinta de la celda seleccionada.

Al aplicar el formato condicional, observa que el formato condicional no se establece correctamente.

Por ejemplo, experimenta este problema cuando usa un programa que incluye código VBA en una hoja de cálculo de Excel similar al código siguiente:

Sub Example()
ThisWorkbook.Worksheets(1).Range("A1").Select
With ThisWorkbook.Worksheets(1).Range("B1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=A1=1"
.FormatConditions(1).Interior.ColorIndex = 46
End With
End Sub

Este código aplica automáticamente el formato condicional a la celda B1 al escribir "1" en la celda A1. Cuando escribe "1" en la celda A1, espera que el color de la celda B1 cambie a rojo. Sin embargo, el color de la celda no cambia. El color de la celda B1 cambia a rojo solo si escribe "1" en la celda B1.

Además, el cuadro de diálogo Formato condicional muestra la fórmula como =B1=1 en lugar de =A1=1.

Solución alternativa

Para evitar este problema, utilice uno de los métodos siguientes.

Método 1: Usar referencias de celda absolutas

Puede usar referencias de celda absolutas para hacer referencia a la celda que contiene la fórmula en lugar de para hacer referencia a referencias de celda relativas.

Por ejemplo, puede modificar la entrada de texto Formula1:="=A1=1" en el código VBA que se describe en la sección "Síntomas" como Formula1:="=$A$1=1" para que el código use referencias de celda absolutas. Esta versión modificada del código VBA es la siguiente:

Sub Example()

ThisWorkbook.Worksheets(1).Range("A1").Select

With ThisWorkbook.Worksheets(1).Range("B1")
     .FormatConditions.Delete
     .FormatConditions.Add Type:=xlExpression, Formula1:="=$A$1=1"
     .FormatConditions(1).Interior.ColorIndex = 46
End With

End Sub

Método 2: seleccione la celda que desea usar para el formato condicional antes de aplicar la fórmula.

Cuando quiera aplicar formato condicional a una celda, seleccione primero la celda que desea usar para el formato condicional. A continuación, seleccione la celda que desea usar para la fórmula. Después de seleccionar esta celda, modifique la fórmula para que se adapte a sus requisitos.

Para ello, siga estos pasos, según corresponda para la versión de Excel que está ejecutando.

Microsoft Office Excel 2007

1. Inicie Excel y, a continuación, abra una nueva hoja de cálculo de Excel.
2. En la hoja de cálculo de Excel, seleccione la celda B1.
3. Haga clic en la pestaña Inicio .
4. Haga clic en Formato condicional en el grupo Estilos y, a continuación, haga clic en Nueva regla.
5. Haga clic en Usar una fórmula para determinar qué celdas aplicar formato enSeleccionar un tipo de regla.
6. Haga clic dentro del cuadro Formato de valores donde esta fórmula es true . A continuación, seleccione la celda que desea usar para el formato condicional.
7. Modifique el valor del paso 6 para que sea =$A$1=1.
8. Haga clic en Formato.
9. En el cuadro de diálogo Formato de celdas , haga clic en la pestaña Relleno .
10. Haga clic en el color "rojo" y, a continuación, haga clic en Aceptar.
11. En el cuadro de diálogo Nueva regla de formato , haga clic en Aceptar.
12. En la celda A1, escriba 1 y presione ENTRAR.
13. Compruebe que el color de la celda B1 cambió a rojo.
14. Cierre la hoja de cálculo de Excel.

Microsoft Office Excel 2003 y versiones anteriores de Excel

1. Inicie Excel y, a continuación, abra una nueva hoja de cálculo de Excel.
2. En la hoja de cálculo de Excel, seleccione la celda B1.
3. En el menú Formato , haga clic en Formato condicional.
4. En Condición 1, haga clic en Fórmula es en la lista.
5. Haga clic dentro del cuadro de entrada de datos. A continuación, seleccione la celda que desea usar para el formato condicional.
6. Modifique el valor del cuadro de entrada de datos para que sea =$A$1=1 y, a continuación, haga clic en Aceptar.
7. Haga clic en Formato.
8. En el cuadro de diálogo Formato de celdas , haga clic en la pestaña Patrones .
9. Seleccione el color "rojo" y haga clic en Aceptar.
10. En el cuadro de diálogo Formato condicional , haga clic en Aceptar.
11. En la celda A1, escriba 1 y presione ENTRAR.
12. Compruebe que el color de la celda B1 cambió a rojo.
13. Cierre la hoja de cálculo de Excel.