Zar tiene la necesidad de aplicar un formato condicional a los valores en la columna A de una hoja de trabajo, pero no puede averiguar cuáles podrían ser las reglas. Además de la columna A, también tiene datos que comienzan en la columna B y agrega periódicamente nuevas columnas de datos. Si hay datos en todas las columnas impares que comienzan con B, Zar quiere que se aplique un formato en la columna A. (B es la columna de datos 1 para su hoja de trabajo, por lo que la considera impar). Si hay datos en todas las columnas pares que comienzan con C, Zar quiere que se aplique un formato diferente en la columna A. Si hay datos en todas las columnas de datos que comienzan con B, por muchos que sean, entonces quiere que se aplique un tercer formato.

Como Zar sin duda ha descubierto, puede crear fácilmente una fórmula para determinar si hay información en las columnas B y C y aplicar el formato en consecuencia. De hecho, una fórmula simple como estas funcionará:

=COUNTA(B1:C1)=2 =COUNTA(B1)=1 =COUNTA(C1)=1

La primera fórmula devuelve Verdadero si hay información tanto en B como en C, la segunda si hay información en B y la tercera si hay información en C. Siempre que seleccione «Detener si es verdadero» para cada regla / fórmula, entonces su formateo funcionará bien.

Crear una fórmula para varias columnas más allá de B y C es solo un poco más difícil. Los mismos tres tipos de fórmulas, en orden, serían los siguientes:

=COUNTA(B1:G1)=6)

=COUNTA(B1,D1,F1)=3 =COUNTA(C1,E1,G1)=3

Puede agregar fácilmente referencias de celda adicionales a las fórmulas, según sea necesario. Tal enfoque devuelve Verdadero en solo tres condiciones: si TODAS las celdas en el rango B1: G1 tienen algo en ellas, si TODAS las celdas impares (B1, D1, F1) tienen algo en ellas y si TODAS las celdas pares (C1, E1, G1) tienen algo en ellos. No devolverá True si solo algunas de las celdas en el rango tienen valores en ellas. Por ejemplo, hay valores en las celdas B1, C1 y E1, entonces no devolverá True y no se cumplirá ninguno de los criterios de formato.

Si bien todos estos funcionan bien con la limitación indicada, no son exactamente lo que está buscando Zar; quiere una fórmula que detecte cuántas columnas se utilizan semana tras semana, mientras continúa agregando datos a las columnas y ajusta el fórmula en consecuencia sin la necesidad de editar manualmente la fórmula para tener en cuenta los datos agregados. En otras palabras, si agrega datos a la columna H, querría que las fórmulas se ajustaran automáticamente para tener en cuenta la columna agregada:

=COUNTA(B1:H1)=7)

=COUNTA(B1,D1,F1,H1)=4 =COUNTA(C1,E1,G1)=3

Evidentemente, esa es una necesidad más compleja. Quizás la mejor manera de abordar el problema es crear una función definida por el usuario (una macro) que pueda observar un rango de celdas y determinar si se cumple uno de los tres criterios.

Considere la siguiente macro:

Function CellChk(crng As Range) As String     Dim iNumOdds As Integer     Dim iNumEvens As Integer     Dim iOdds As Integer     Dim iEvens As Integer     Dim iTots As Integer     Dim iTotCells As Integer     Dim rWork As Range     Dim rCell As Range     Dim iLastCol As Integer     Dim sTemp As String

iOdds = 0     iEvens = 0     iTots = 0

' Figure out the real last column in the worksheet and set range     iLastCol = ActiveSheet.Cells.Find(What:="*", _         SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _         LookIn:=xlFormulas).Column     Set rWork = Range(Cells(crng.Row, 2), Cells(crng.Row, iLastCol))



iTotCells = rWork.Count     iNumOdds = (iTotCells + 1) \ 2    ' Number of odd columns     iNumEvens = iTotCells - iNumOdds  ' Number of even columns

For Each rCell In rWork         If rCell <> "" Then             If ((rCell.Column - 1) Mod 2) = 1 Then                 iOdds = iOdds + 1             Else                 iEvens = iEvens + 1             End If             iTots = iTots + 1         End If     Next rCell

sTemp = ""

If iTots = iTotCells Then         sTemp = "t"

ElseIf iOdds = iNumOdds Then         sTemp = "o"

ElseIf iEvens = iNumEvens Then         sTemp = "e"

End If     CellChk = sTemp End Function

Utiliza la macro pasándole una dirección en la fila que desea verificar.

Entonces, por ejemplo, si estuviera aplicando la regla de formato condicional a la celda A3, pasaría a la macro una dirección de B3 o C3, cualquier cosa excepto A3, ya que eso generará una referencia circular. La macro busca la última celda utilizada en esa fila y luego determina cuántas celdas pares e impares tienen algo en ellas. La macro devuelve cualquiera de los cuatro valores; si se cumple el primer criterio (todas las celdas de la fila que comienzan con la columna B tienen algo), se devuelve una «t». Si todas las columnas impares (siendo B la primera columna impar) tienen algo en ellas, se devuelve «o». Si todas las columnas pares (siendo C la primera columna par)

tienen algo en ellos, luego se devuelve «e». Si no se cumple ninguno de los tres criterios, la función no devuelve nada.

Aún necesitará configurar tres reglas de formato condicional que se basan en la evaluación de una fórmula. Aquí hay tres que puede usar con esta macro:

=CellChk(B1)="t")

=CellChk(B1)="o")

=CellChk(B1)="e")

Estos ejemplos son para aplicar un formato condicional a la celda A1; ajuste las referencias de celda a la fila correcta que desea que analice la macro. Recuerde que, aunque especifique una sola celda (B1 en estos ejemplos), la macro calcula cuántas celdas en la fila debe mirar realmente.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (5945) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.