VBA: Formato condicional en Excel según valores repetidos.

Veremos hoy una aplicación sencilla de una macro que identifica qué elementos de un rango se han repetido más de x veces, para luego asignarles a éstos, un color diferente según agrupaciones.
El ejemplo siguiente trata de dar una solución a la pregunta dada por un lector del blog:

...tengo varias columans con mas de 10000 registros o lineas y quisiera poner un color diferente para cada dato que se repita 30 veces...

Supondremos tenemos un listado de unos cuantos registros numéricos, algunos de los cuales están repetidos. Por simplificar, tendremos un listado de 17 elementos (positivos y negativos):

Se trata, por tanto, de asignar un color diferente a cada agrupación de valores repetidos, siempre que se repitan un número de veces (para nosotros tres o más).
Para ello, haremos un uso extensivo de las funciones VBA y alguna función de hoja de cálculo, todo ello como parte de nuestra macro.
Una de ellas, ya que se trata de dar color a las celdas, será la función VBA RGB(rojo, verde, azul): cada uno de los argumentos debe ser de tipo Variant ó Integer, y con un valor dentro del intervalo de 0-255, ambos inclusive, que representa el componente rojo del color (valores mayores los admite como 255).
Otras funciones que emplearemos son la de MAX y MIN que aplicaremos sobre nuestro rango de trabajo, para conseguir los valores mínimo y máximo del rango, datos sobre los que luego realizaremos unos cálculos. También usaremos las funciones INT y ABS, para determinar el valor absoluto y quedarnos con la parte entera del cálculo que realizaremos. Y finalmente otra función de hoja de cálculo: CONTAR.SI, que llamaremos desde nuestra macro de la siguiente manera:
Application.WorksheetFunction.CountIf(rango, criterio)

Insertaremos en un módulo del Explorador de proyectos del Editor de VBA el siguiente código, formando nuestra macro de Excel:

Sub ColorearRepetidos()
Dim rng As Range
Set rng = Range("A2:A18")

Static rojo As Integer, verde As Integer, azul As Integer
Static contador As Integer

For Each celda In rng
contador = Application.WorksheetFunction.CountIf(rng, celda.Value)
rojo = Abs(Int(255 * (celda.Value / Application.Max(rng))))
verde = Abs(Int(255 * (Application.Min(rng) / celda.Value)))
azul = Abs(Int(rojo + (verde / celda.Value)))
    If contador >= 3 Then
    Range(celda, celda.Offset(0, 3)).Interior.Color = RGB(rojo, verde, azul)
    Else
    Range(celda, celda.Offset(0, 3)).Interior.Color = xlNone
    End If
Next celda
End Sub

las variables estáticas rojo, verde y azul responden a diferentes fórmulas, que devuelven el mismo valor para cada elemento, de esta forma nos aseguramos que cualquier elemento obtiene tres valores iguales, independientemente de dónde se encuentre en el listado origen.
Con la sentencia IF...THEN... evaluamos las veces que se encuentra repetido el elemento en total, ya que sólo queremos dar color a los repetidos tres o más veces, en cuyo caso, daremos color al fondo de la celda con la función RGB, y argumentos el resultado del cálculo rojo, verde y azul.
Como el lector pedía dar color a toda la fila de la base de datos, he creado un rango por fila al que asignar color con la propiedad OFFSET.
Podemos ver el resultado tras ejecutar nuestra macro:

Aparentemente podría parecer que hay varios colores iguales para diferentes elementos, pero esto es sólo un problema de resolución de pantalla o de nuestra tarjeta gráfica o incluso de nuestra vista, ya que si emplearamos un editor de imágenes cualquiera que pueda identificar con exactitud colores, veríamos que efectivamente son colores distintos.
Podemos hacer incluso una prueba, replicando con fórmulas los valores calculados con nuestra macro:

Siendo la fórmula para la columna Rojo:
=ABS(ENTERO((255*(A2/MAX($A$2:$A$18)))))
Para Verde:
=ABS(ENTERO((255*(MIN($A$2:$A$18)/A2))))
Y para Azul:
=ABS(ENTERO(B2+(C2/A2)))
Evidentemente podríamos haber optado por cualquier fórmula y valor, pero esta es una forma de asegurarse igual valor para cada color (rojo, verde, azul) para cada elemento repetido.
Si comprobamos ahora con un editor de imágenes la composición de cada color, para los valores 100 y -100, veríamos efectivamente que son colores distintos...

Por supuesto, este procedimiento tiene varias limitaciones, pero para ciertas bases de datos numéricas puede ser bastante útil...