Contar celdas amarillas en Excel para sumar horas extra (UDF VBA paso a paso)

¿Necesitas que tus totales de horas se actualicen solos cuando marcas en amarillo las clases de 5 h? Aprende a contar automáticamente las celdas coloreadas, sumar la hora extra y entender por qué solo puede lograrse con VBA o cambiando la lógica de tu hoja.

Índice

Por qué aprovechar el color como indicador

En muchas plantillas académicas se usa el color de fondo para resaltar sesiones especiales: exámenes, clases dobles, actividades externas, etc. Es un lenguaje visual que todos entienden al instante. Sin embargo, Excel no interpreta los colores como “datos” en sus funciones nativas; por eso, si el color implica un cambio de duración (5 h en vez de 4 h) necesitamos decirle a Excel qué significa “amarillo”.

Limitaciones de las fórmulas estándar

Funciones como COUNTIF, SUMIF o FILTER solo trabajan con valores (números, texto, fechas) y no con formatos. Por ejemplo, =COUNTIF(D13:N13, "<>") cuenta celdas no vacías, pero ignora por completo si están coloreadas. Tampoco es posible anidar una llamada directa a CELDA() o a INFO() para obtener el color de relleno.

En consecuencia, tenemos dos caminos:

Introducir la información “clase de 5 h” en una columna auxiliar (valor booleano) y basarnos en ella → solución 100 % sin VBA.
Crear una función definida por el usuario (UDF) en VBA que cuente los rellenos amarillos y devuelva el total.

Creación de la función personalizada `CountYellow`

Paso a paso dentro del editor de VBA

Presiona Alt+F11 para abrir el IDE de Visual Basic.
En el panel izquierdo, haz clic en tu libro (por ejemplo, Libro1.xlsm).
Menú Insert ▸ Module. Con ello generas un módulo estándar (Módulo1).
Copia y pega el siguiente código:

Option Explicit

'Función que cuenta cuántas celdas de un rango tienen fondo amarillo
Public Function CountYellow(rng As Range) As Long
    Dim cel As Range
    Application.Volatile           'Obliga a recalcular al pulsar F9
    For Each cel In rng
        If cel.Interior.Color = vbYellow Then
            CountYellow = CountYellow + 1
        End If
    Next cel
End Function

Código completo explicado

Instrucción	Propósito
`Option Explicit`	Exige declarar todas las variables; evita errores por nombres mal escritos.
`Public Function CountYellow(rng As Range) As Long`	Declara una UDF pública que recibe un rango y devuelve un número entero largo.
`Application.Volatile`	Hace que la función se recalculé al pulsar `F9`; Excel no detecta cambios de formato automáticamente.
`For Each cel In rng`	Itera por cada celda del rango.
`If cel.Interior.Color = vbYellow`	Comprueba si el color de relleno coincide con el amarillo estándar.
`CountYellow = CountYellow + 1`	Suma 1 por cada coincidencia.

Guarda el libro como .xlsm; de lo contrario, el código se perderá.

Aplicación práctica en la hoja

Supongamos que en las columnas D a N se registran las fechas de clase de cada alumno y que en O resides las horas estándar (4 h). La fórmula original era algo parecido a:

=SI(O13="";"";O13*4 + 2)  'Suma 2 h manuales cuando corresponde

Esa suma manual es propensa a olvidos. Sustitúyela por:

=SI(O13="";"";O13*$N$10 + CountYellow(D13:N13))

$N$10 contiene el factor por hora (4 h).
CountYellow(D13:N13) devuelve cuántas celdas están en amarillo en esa fila.

Si pintas en amarillo dos fechas, la fórmula añade 2 h; si quitas el color, resta automáticamente tras pulsar F9.

Ilustración con una tabla de ejemplo

Fila	Fechas (D–N)									Horas totales (`=O13*$N$10+…`)
13 (Ana)	05‑ene	07‑ene	12‑ene	19‑ene	26‑ene					10
14 (Luis)	06‑ene	08‑ene	15‑ene	22‑ene						9

Ana tiene dos fechas en amarillo → 8 h básicas + 2 h extra = 10 h. Luis tiene una → 8 h básicas + 1 h extra = 9 h.

Macro versus función definida por el usuario

Aunque ambos se programan en VBA, conviene distinguir:

Macro: procedimiento Sub sin valor devuelto. Se ejecuta desde el menú Macros, un botón o un atajo. Ejemplo: ordenar una lista o exportar un PDF.
UDF: procedimiento Function con parámetros y valor de retorno. Se llama dentro de una celda igual que SUM o VLOOKUP. No muestra cuadros de diálogo ni modifica otras celdas (salvo que lo forces, lo cual no se recomienda).

Mantenimiento y recálculo totalmente automático

La llamada a Application.Volatile ya obliga a recalcular al presionar F9, pero si deseas que la hoja se actualice en cuanto cambies un color (sin pulsar nada) puedes interceptar el evento WorksheetSelectionChange o WorksheetChange:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Calculate       'Recalcula solamente la hoja activa
End Sub

Pega ese código en el módulo de la hoja correspondiente (no en un módulo estándar). Ten en cuenta que recalcular constantemente una hoja grande puede percibirse más lento.

Alternativas 100 % sin VBA

Si tu entorno de trabajo prohíbe macros o necesitas compatibilidad con Excel Online, la mejor opción es introducir la bandera “clase de 5 h” como dato explícito. Por ejemplo:

Añade una columna Duración justo después de la fecha.
En lugar de pintar de amarillo, escribe 5 en la columna Duración si la clase dura 5 h; deja 4 para la normal.
La fórmula total se simplifica a =SUM(Duración).

La estética se puede mantener con Formato condicional: regla que coloree en amarillo las celdas con valor 5. Así separas el dato (5 h) del adorno (color) y eliminas definitivamente la necesidad de VBA.

Buenas prácticas al trabajar con formatos

Evita basar cálculos críticos solo en colores; úsalos como apoyo visual.
Define un esquema cromático claro y documentado (amarillo = 5 h, rojo = cancelada…).
Protege la hoja para que usuarios inexpertos no cambien el color accidentalmente.
Realiza copias de seguridad regulares; el formato puede perderse al pegar desde otras fuentes.

Preguntas frecuentes (FAQ)

¿La función CountYellow ralentizará mi libro?

El impacto es mínimo mientras el rango evaluado sea razonable (cientos o unos pocos miles de celdas). Si procesas decenas de miles, notarás retraso al recalcular.

¿Qué ocurre si uso otro tono de amarillo?

vbYellow corresponde a RGB(255, 255, 0). Si empleas un amarillo distinto deberás comparar con .Interior.Color igual al valor exacto (por ejemplo, 65535) o con .Interior.ColorIndex.

¿Puedo contar varios colores con una sola función?

Sí. Declara un parámetro adicional ColorBuscar As Long y pasa el rango junto al valor RGB deseado. Devuelve el recuento que coincida con ese color.

¿Funciona en macOS y en versiones antiguas de Excel?

Sí. La interfaz de VBA es la misma desde Excel 2000. Solo asegúrate de guardar el archivo como .xlsm y de habilitar macros al abrirlo.

Conclusión

Con una sencilla UDF en VBA es posible traducir un formato visual (celdas amarillas) en un dato cuantificable y mantener la hoja limpia de sumas manuales “+2”. Si VBA no es viable, registra la duración real en una columna auxiliar y apóyate en formato condicional para conservar la claridad visual. En ambos casos lograrás totales precisos y una plantilla más profesional.