jueves, 23 de enero de 2014

VBA: Obtener el color de relleno o fuente de una celda (con o sin formato condicional).

En varias ocasiones me han preguntado por la forma de determinar el color de relleno o de la fuente de una celda, en especial, cuando dicha celda tiene configurado un Formato condicional... obviamente, para determinar tal color (de relleno o de fuente) se necesita algo de programación.
El problema es que no existe una forma directa de obtener el color de una celda cuando lo que tiene aplicado es un Formato Condicional, ya que las propiedades estándar .Color o .ColorIndex sobre la celda de estudio no devuelve valor (o mejor dicho lo devuelve como sin color) cuando lo que tiene es un formato condicional aplicado.

Me puse a investigar y encontré esta Function que desarrolló Rick Rothstein, la cual adapté un poco y a la que he incorporado las explicaciones oportunas.

Fijémonos en la imagen:

VBA: Obtener el color de relleno o fuente de una celda (con o sin formato condicional).


donde las celdas B2 y B12 sólo tiene aplicado un formato de celda 'estándar', mientras que las celdas del rango B3:B11 tienen aplicado el siguiente formato condicional:

VBA: Obtener el color de relleno o fuente de una celda (con o sin formato condicional).



La función que crearemos está construida sobre tres argumentos (dos de ellos opcionales, pero predefinidos como VERDADERO); el primero será la celda (sólo UNA!!) que queremos analizar, el segundo conforma si obtendremos el color de relleno o de la fuente (según un valor Booleano - V ó F), y el tercer argumento define si emplearemos la propiedad .Color o .ColorIndex para conseguir el código del color de Fuente o Relleno (tmabién utilizando un valor Booleano).

En un módulo del editor de VB incluimos la siguiente Function:

Function ColorCelda(celda As Range, _
        Optional ColorRellenoCelda As Boolean = True, _
        Optional ReturnColorIndex As Long = True) As Long
' ColorRellenoCelda - Opcional, tipo Boolean (valor por defecto = TRUE)
'                VERDADERO hace que nuestra función nos devuelva el color de Relleno,
'                según la propiedad de .Color o .ColorIndex
'                determinada en el valor del tercer argumento ReturnColorIndex
'                FALSO  hace que nuestra función nos devuelva el color de la Fuente,
'                según la propiedad de .Color o .ColorIndex
' ReturnColorIndex - Opcional, tipo Boolean (valor por defecto = TRUE)
'                    VERDADERO hará que nuestra función emplee la propiedad .ColorIndex
'                    FALSO hará que nuestra función emplee la propiedad .Color

Dim X As Long
Dim Test As Boolean
Dim CeldaActiva As String

Application.Volatile

CeldaActiva = ActiveCell.Address
'recorremos todos los formatos condicionales existentes en la celda de estudio
For X = 1 To celda.FormatConditions.Count
  With celda.FormatConditions(X)
    'si la condición responde al Valor de una celda
    'sabiendo que .Formula1 y .Formula2 son los valores que podemos informar al configurar nuestro formato condicional
    If .Type = xlCellValue Then
      Select Case .Operator
        'Evaluate equivaldría a la función INDIRECTO de la hoja de cálculo
        'convierte, por tanto, un nombre de Microsoft Excel en un objeto, valor o referencia.
        Case xlBetween:      Test = celda.Value >= Evaluate(.Formula1) And celda.Value <= Evaluate(.Formula2)
        Case xlNotBetween:   Test = celda.Value <= Evaluate(.Formula1) Or celda.Value >= Evaluate(.Formula2)
        Case xlEqual:        Test = Evaluate(.Formula1) = celda.Value
        Case xlNotEqual:     Test = Evaluate(.Formula1) <> celda.Value
        Case xlGreater:      Test = celda.Value > Evaluate(.Formula1)
        Case xlLess:         Test = celda.Value < Evaluate(.Formula1)
        Case xlGreaterEqual: Test = celda.Value >= Evaluate(.Formula1)
        Case xlLessEqual:    Test = celda.Value <= Evaluate(.Formula1)
      End Select
    'si por contra la condición corresponde a una expresión o fórmula
    ElseIf .Type = xlExpression Then
      Application.ScreenUpdating = False
      celda.Select
      Test = Evaluate(.Formula1)
      Range(CeldaActiva).Select
      Application.ScreenUpdating = True
    End If

    'Verificamos nuestra comparativa Test, cuando sea VERDADERA
    If Test Then
      If ColorRellenoCelda Then
        'y además nuestro segundo y tercer argumento se han informado como CIERTOS
        'controlando el tercer argumento con la función VBA IIf
        ColorCelda = IIf(ReturnColorIndex, .Interior.ColorIndex, .Interior.Color)
      Else
        'pero si nuestro segundo argumento es FALSO y el tercer argumento se ha informado como CIERTO
        'controlando el tercer argumento con la función VBA IIf
        ColorCelda = IIf(ReturnColorIndex, .Font.ColorIndex, .Font.Color)
      End If
      'salimos de la función...
      Exit Function
    End If
  End With
Next
  
'si no tuviera Formato condicional aplicado...
If ColorRellenoCelda Then
  'y si hubieramos indicado como CIERTO el segundo argumento
  ColorCelda = IIf(ReturnColorIndex, celda.Interior.ColorIndex, celda.Interior.Color)
Else
  'y si hubieramos indicado como FALSO el segundo argumento
  ColorCelda = IIf(ReturnColorIndex, celda.Font.ColorIndex, celda.Font.Color)
End If
End Function

De especial interés en esta Function es el empleo que se hace de la propiedad .Type del formato condicional (.FormatConditions(i).Type), con el que averigüamos o controlamos el tipo de Formato Condicional de la celda analizada.
Especifica si el formato condicional está basado en el valor de una celda.
Nombre Valor Descripción
xlCellValue --- 1 --- Valor de celda
xlExpression --- 2 --- Expresión
xlColorScale --- 3 --- Escala de colores
xlDatabar --- 4 --- Barra de datos
xlTop10 --- 5 --- 10 valores más frecuentes
XlIconSet --- 6 --- Conjunto de iconos
xlUniqueValues --- 8 --- Valores únicos
xlTextString --- 9 --- Cadena de texto
xlBlanksCondition --- 10 --- Condición de celdas que están en blanco
xlTimePeriod --- 11 --- Período de tiempo
xlAboveAverageCondition --- 12 --- Condición por encima de la media
xlNoBlanksCondition --- 13 --- Condición de celdas que no están en blanco
xlErrorsCondition --- 16 --- Condición de errores
xlNoErrorsCondition --- 17 --- Condición sin errores
Es decir, si se trata de un Formato condional asociado al Valor de la celda:

VBA: Obtener el color de relleno o fuente de una celda (con o sin formato condicional).

O bien un Formato condicional basado en una expresión o fórmula:

VBA: Obtener el color de relleno o fuente de una celda (con o sin formato condicional).

Una vez chequeado si la celda cumple alguna de los criterios de los formatos condicionales, en caso afirmativo, con la funicón VBA IIf dirigimos nuestra function ColorCelda hacia la propiedad .Color o .ColorIndex; que de una manera directa tendría la forma:
celda.FormatConditions(X).Interior.ColorIndex
La Function 'ColorCelda' finaliza controlando el caso en que en la celda de estudio no hay aplicado formato condicional...

37 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. Hola Ismael, tengo un problema con excel y es el siguiente:

    Tengo 3 columnas G,N y O.

    La columna G está formada por numeros donde algunas celdas se rellenarán de un color personalizado designado por formato condicional "Es igual a" desde otras celdas de otras columnas.

    La columna N está formada igualmente por numeros pero solo desde 0 al 6 aleatoriamente, donde las celdas con valor 3 tendrán un color, con valor 4 otro color y asi sucesivamente hasta el valor 6. Todas las celdas aplicadas por la misma regla de formato condicional "Es igual a".

    Mi pregunta es si se puede hacer esto: cuando una celda coloreada por formato condicional de la columna G por ejemplo G8 coincide con otra celda contigua coloreada con formato condicional que en este ejemplo sería la celda contigua N8 yo quiero que en la celda contigua de la columna O, es decir en celda O8 muestre un texto cuando estas dos condiciones suceden. ¿Es posible? Gracias de antemano. Saludos.

    ResponderEliminar
    Respuestas
    1. Hola Andrés,
      si sería posible.. bastaría aplicar un condicional en O8, teniendo en cuenta que los Formatos condicionales responden a una regla, en definitiva a una condición.. por tanto, bastaría replicar de nuevo en una fórmula del tipo SI las dos condiciones de G8 y N8
      Podría ser algo así:
      =SI(Y(condición G8;condición N8);"texto a mostrar si cumplen";"caso contrario")

      Espero te oriente...
      Un cordial saludo

      Eliminar
    2. Muchas gracias por la aclaración, pero el problema es que no se como expresar la condición de G8 y de N8, es decir, por ejemplo, tengo en celda G8 color rojo de relleno dado por formato condicional y celda N8 en color de relleno verde igualmente consignado ambos por formato condicional "Es igual a" , pues por ejemplo cuando ocurra el caso de que coincida que G8 esté relleno de rojo y N8 esté relleno de verde me escriba un texto como este "premiado" en celda O8, mi problema es que no se como aplicar las condiciones de G8 y N8 con la fórmula que me has dado con lo que quiero hacer, es decir había pensado en poner en condicion 1 como que G8= al color rojo y como segunda condición N8= al color verde todo lo demas de la formula conozco como hacerlo pero el problema es como le digo en las condiciones de G8 y N8 para que reconozca ese color y se cumplan ambas condiciones de G8= color rojo y N8= color verde, conozco los códigos RGB de cada color pero no se si se podria aplicar con esta formula condicional que me has citado anteriormente o hacerlo en VBA ¿cómo sería la condición? si se puede hacer claro.

      Eliminar
    3. Hola Andres,
      si has indicado una condición (en el Fc) igual a , la condición será
      celda=...
      al ser doble
      =Y(celda1=...M;celda2=...)

      Lo siento, pero no puedo ser más explicito sin conocer cuáles son esas condiciones de igualdad...

      Envíame el fichero a
      excelforo@gmail.com
      y trato de echarle un vistazo.

      Slds

      Eliminar
    4. Muchas gracias por todo Ismael, he conseguido al fin solucionarlo y ya me funciona lo que quería hacer. Saludos!

      Eliminar
  3. Hola quisiera saber si es posible hacer lo siguiente en excel...
    Tengo una hoja de trabajo x, y en la celda p obtengo informacion con la funcion buscarv, que me dice cual es el proveedor con el precio minimo durante el mes segun consulto el articulo comprado, yo quisiera que cada vez que consulto un articulo el proveedor que me da el mejor precio se vaya copiando en otra hoja, y se agreguen los demas proveedores con mejor precio de los articulos consultados...
    Es posible??

    ResponderEliminar
    Respuestas
    1. Hola,
      si es posible, pero necesitarías un procedimiento Sub (una macro) asociado a un evento de cambio _Change de la hoja donde esté la fórmula (hoja x).. esta macro realizaría el proceso de copiado y pegado a la segunda hoja, a continuación del último copiado.

      Saludos cordiales

      Eliminar
  4. Hola Ismael el código no me corre ya que la expresión en formato condicional esta en español y cuando pasa en VBA no corre ejemplo "=ESTEXTO(A2)" ingles "=ISTEXT(A2)" en la instrucción Test = Evaluate(.Formula1) ya que el contenido esta aun en español y corrió correcto cuando coloque Test = Evaluate("=ISTEXT(A2)") como puedo corregir esto.

    ResponderEliminar
    Respuestas
    1. Hola Luis,
      pues he estado rebuscando y termino de encontrar una solución...
      el problema es que lo pasa literalmente.
      Se me ha ocurrido generar un Nombre definido (llamemoslo FormText) con la fórmula descrita =ESTEXTO(A2)
      y luego generar el formato condicional empleando ese Nombre definido, en vez de la fórmula directa =ESTEXTO(A2)

      así, EVALUATE reconocerá y trasladará correctamente la función...

      El problema es que no corre como debiera...

      Siento no poder ayudarte
      :'(

      Eliminar
  5. hola, muy buen post....necesito me ayuden con un código vba, soy principiante en esto de programacion y me interesa mucho aprender:
    obtener el color de celda que esta en formato condicional
    celda B2 (edad x nacimiento), C2 (edad que manifiesta); E2(dice la verdad o no); rojo mentira y amarillo verdad...quisiera saber el color de fondo de las celdas condicionales

    ResponderEliminar
    Respuestas
    1. Hola Percy,
      solo tendrías que aplicar la función personalizada que aparece en este post para descubrir el color del formato condicional.
      Un saludo

      Eliminar
  6. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  7. HOLA, NECESITO SABER CUANDO UNA CELDA ESTA EN AMARILLO ME DE UN NUMERO 2, SI ESTA EN ROJO UN NUMERO 3 Y SI ESTA EN VERDE ME DE UN NUMERO 4, COMO ES POSIBLE, SALUDOS Y MUCHAS GRACIAS DE ANTEMANO

    ResponderEliminar
    Respuestas
    1. Hola,
      trabajando sobre la función del post, evaluas un condicional:
      =SI(ColorCelda(...)=3;2;SI(ColorCelda(...)=10;3;SI(ColorCelda(...)=13;4)))
      ajustando los valores e igualdades a los números de color devueltos por la función UDF ColorCelda

      otras opciones recorriendo con un bucle en una macro el rango y aplicando un condicional...

      Slds

      Eliminar
  8. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  9. Buena Tarde Ismael,
    Estoy intentando lo siguiente y me gustaria, si es posible, leer tu solucion o comentario:
    Las celdas A1, B1 y C1 me dan tres numeros al azar entre 0 y 255 para juntos mostrar un codigo RGB para la celda A2.
    Es posible hacer que el color de fondo de la celda A2, corresponda con el codigo RGB creado? tal que:
    Color de fondo para A2: (Valor"A1"),(Valor"B1"),(Valor"C1").
    Tambien me gustaria que las celdas reciban el color codigo RGB correspondiente segun:
    Color de fondo para A1: (Valor"A1"),0,0;
    Color de fondo para B1: 0,(Valor"B1"),0 y
    Color de fondo para C1: 0,0,(Valor"C1").

    ResponderEliminar
    Respuestas
    1. Hola,
      necesitarás una macro de este estilo:
      Sub color()
      Range("A2").Interior.color = RGB(Range("A1").Value, Range("B1").Value, Range("C1").Value)
      End Sub
      y repetir tres líneas más para los tres últimos casos
      Saludos

      Eliminar
    2. Muchas Gracias Ismael ^^

      Eliminar
  10. Hola Ismael, primero que nada muchas gracias por el codigo propuesto aqui, me ha servido de mucho, lo unico que quisiera saber es como hacer para que no me de error, necesito usar tu funcion pero con el "xlColorScale", pero al colocarlo en la funcion me da error. Imagino que es porque no estoy cambiando las demas acciones en la funcion.

    Ok, explico un poco que deseo hacer, tengo una celda combinada (A2:B2) el la Hoja1 en la cual quiero que refleje junto a las dos celdas de arriba (tanto A1 como B1 por separado) el color de relleno de una celda con formato condicional en la Hoja2 (supongamos F4), hasta ahora con la funcion que propones solo me regresaria (cuando funcione) el valor del color del fondo, pero no me funciona con foratos condicionales. Si es posible, podria enviarte el archivo que quiero modificar para que puedas ver mejor lo que quiero hacer.

    ResponderEliminar
    Respuestas
    1. Hola,
      no termino de comprender del todo qué necesitas...
      pero la macro sí te devuelve el color incluso si las celdas tienen formato condicional...
      quizá el fallo esté motivado por la celda combinada...
      Slds

      Eliminar
    2. No, eso es lo que quiero realizar a futuro, pero en este momento estoy realizando la prueba de la macro en una sola celda y no funciona, estoy probandola de esta manera: en la Celda R3 (color agregado por mi arroja numero 23 ya que es azul) pero desde la Celda R4 hasta la Celda R7 son colores en degradado por funcion de escala de colores y solo arroja (en todas) -4142

      Eliminar
    3. Hola,
      el problema es que la propiedad para el 'color degradado' es
      .Interior.Gradient.ColorStops(1).Color
      .Interior.Gradient.ColorStops(2).Color
      etc

      que no se tiene en cuenta en la macro, dentro la gestión del código...
      Si vas a incluir combinaciones de colores deberás incluir esta posibilidad en la programación.
      Pero ojo, por que necesitarás dos celdas para recuperar los dos colores del degradado, o unirlas con algún join en una sola.

      Slds

      Eliminar
    4. Muchas gracias Ismael, pero no tengo idea de como hacer la modificacion de la funcion para la escala de colores, si puedes ayudarme seria genial.

      Eliminar
    5. Hola,
      intentaré cuanto tenga tiempo subir esta personalización.
      Slds

      Eliminar
  11. Muchas gracias publicar esta macro, es excactamente lo que encesito , pero lamentablemente no me esta funcionando. Me arrojo siempre el mismo numero independientemente de color que que tenga la celda ,14. Estoy usando tres tipos de colores para el formato condicional Verde Rojo y amarillo. Espero puedas ayudarme

    ResponderEliminar
    Respuestas
    1. ummm raro,
      subiré otro post con una solución alternativa para recuperar el color en todo caso.
      Saludos

      Eliminar
  12. Tengo el siguiente problema, ejemplo: en una lista de la celda A1 a A10 tengo celdas con valores y color especifico (A1 = 10 color rojo, A5 =20 Color rojo, A7 = 2 color amarillo, etc) necesito que en la columna c me aparezcan solo los valores del color rojo que seria los valores que necesito ver en esa nueva columna

    quedo atento a su respuesta

    ResponderEliminar
    Respuestas
    1. Hola, qué tal?
      un placer saludarte igualmente.
      Puedes aplicar la función descrita en el post anidándola dentro de un condicional que reconozca si el color es el rojo..
      Un cordial saludo

      Eliminar
  13. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  14. Porque la aplicacion de la funcion colorcelda devuelve error al evaluar una celda con formato condicional en el cual la fuente toma color acorde con una formula de formato condicional? Con celdas sin formato o con formato no condicionado por formula funciona bien.
    Agradezco opinion y saludos a toos

    ResponderEliminar
    Respuestas
    1. Hola,
      no debería... la función precisamente (como se muestra en el ejemplo) está preparada para detectar cualquier tipo de formato (normal o condicional...)
      Fíjate en las filas 39 a 45 del código, donde precisamente recoge tu caso.. esto es, cuando el formato condicional responde a una fórmula.
      Slds

      Eliminar
    2. Buenas tardes Ismael..
      Tengo aplicado en la celda A1 un formato condicional..
      Necesito poner en la celda B2 una formula que me diga si A1 tiene formato condicional o no..

      Qué formula puedo aplicar en B2?

      Gracias anticipadas..

      Eliminar
  15. Trabajando con esta función posteada..

    ResponderEliminar
  16. Es que me sale -4241.. estoy aplicando un formato condicional para valores duplicados en un rango.

    ResponderEliminar
    Respuestas
    1. Hola Jorge
      la función del post está pensada para reglas estándares que usan operadores (mayor que, menor que, etc.)... para que considere la regla de duplicados habría que añadir un nuevo elseif que controle esa propiedad.
      La regla de duplicados se controla con:
      .FormatConditions(1).DupeUnique = xlDuplicate
      asi pues esta es la opción a incluir...
      Saludos cordiales

      Eliminar