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...

15 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