Formato Condicional Ajustado a columnas

Veremos hoy cómo aplicar un sencillo formato condicional para mostrar celdas formateadas de acuerdo al valor de una tercera celda.
Este post nace por la necesidad expresada por una lectora:

[...]existe la posibilidad de pintar celdas en Excel con un condicional que diga coloree las siguientes x numero de celdas, por ejemplo: Numero de piezas: 10 Entonces necesito que cambie el color de diez celdas para que sobre ellas se puedan anotar los nombres de cada pieza.[...]

Veamos la resolución del caso concreto para valores de 1 hasta 10.
1- Seleccionamos el rango B2:K11. Me aseguro que mi celda activa es B2.
2- Accedo al formato condicional > Nueva Regla > Utilice una fórmula que determine las celdas para aplicar formato.
3- Introducimos la siguiente fórmula:
=COLUMNA()<=$A2+1

Listo. Con esa simple fórmula solucionamos el caso propuesto por nuestra amiga.
La fórmula aplicada únicamente compara la columna de cada celda con el valor de la celda (columna A)-ajustada con un +1 para concretar correctamente columna vs valor.

Este formato condicional expuesto funciona correctamente, sin duda... pero tiene una deficiencia difícil de combatir. Y es que manualmente tendríamos que ajustar/modificar nuestra regla de formato condicional, en cuanto a la propiedad 'Se aplica a' para el caso en que el intervalo de los datos (columna A) no estuviera claramente delimitado entre 0 y 10.
Dicho de otro modo, cómo ajustar el rango donde aplicar nuestra regla independientemente de los datos a transformar (sean 10 o 200).

Tenemos, pues, que conseguir que se autoajuste el rango donde aplicar nuestra regla.
Por desgracia el campo donde incluimos la propiedad 'Se aplica a' no admite fórmulas, ni nombres definidos... solo y exclusivamente rangos (como objeto), lo que hace imposible con el estándar obtener el dinamismo perseguido.
Por tanto lograremos nuestra meta con una macro.

Nuestra macro siguiente recorrerá las posibles reglas de formato condicional existentes en nuestra hoja de trabajo, buscando aquella cuya fórmula aplicada coincida con la descrita anteriormente (=COLUMNA()<=$A2+1), en cuyo caso aplicando el método .ModifyAppliesToRange sobre dicho formato condicional.
Este método modificará el rango donde aplicar la regla, tal como queríamos; quedando ajustada al rango exacto según el valor máximo.

Para facilitar el trabajo calculamos en la celda A1 el dato máximo ( =MAX(A2:A11) ), que emplearemos en nuestra macro.

Añadimos el siguiente código en un módulo estándar de nuestro proyecto de VB... aunque también podríamos asociar la macro a un evento de hoja tipo _Change:

Sub CambioFC()
Dim FC As FormatCondition
Dim sh As Worksheet

Set sh = Hoja2
For Each FC In sh.Cells.FormatConditions
    '.AppliesTo.Address, .Type, .Formula1, .Interior.Color, .Font.Name
    'buscamos la regla de formato condicional con nuestra fórmula
    If FC.Formula1 = "=COLUMNA()<=$A2+1" Then
        'y modificamos el rango donde aplicar la regla de formato condicional
        'ajustando la celda final del rango con el valor máximo de los datos
        FC.ModifyAppliesToRange Range(Cells(2, "B"), Cells(11, Range("A1").Value + 1))
    End If
Next FC
End Sub

Al ejecutar la macro conseguimos nuestro objetico de modificar y ajustar el rango donde aplicar nuestra regla de formato condicional.