Prueba logica con color de fondo de una celdaOn 07/08/2010, in Macros, Macros Excel, by dcedeno Descripcin de cmo emplear como prueba lgica el color de fondo de una celda, apoyados en una funcin desarrollada por el MVP de Excel Chip Pearson.

6

hare ail 6

inShare1 Email

Hola! Cmo han estado? Recientemente uno de nuestros usuarios nos hizo una interesante pregunta, en la cual manifest lo siguiente: "Es posible poner un color de fondo como prueba lgica dentro de la funcin SI de Ms Excel?"La respuesta es s, existen varios mtodos con los que podemos construir la funcin a partir de esa premisa sin embargo esta vez vamos a emplear una funcin desarrollada por el MVP de MS Excel Chip Pearson en su blog www.cpearson.com la cual nos va a permitir elaborar una funcin que identifica el color de fondo de una celda para elaborar con ella luego frmula que emplee la funcin SI para aplicar una prueba lgica al color de fondo. Es necesario aplicar esta tcnica dado que MS Excel por defecto no trae una funcin que permita identificar el color de fondo de una celda, debemos construirla a travs de Visual Basic. Como todo lo dems que existe en el mundo de la computacin los colores son realmente un nmeros. Todos los colores que se muestran en la pantalla de la computadora se encuentran definidos a partir de combinaciones de tres componentes primarios: uno rojo, uno verde y uno azul. Esto es lo que se conoce como cdigo RGB. Visual Basic para Aplicaciones dispone de una funcin RGB que podemos emplear para combinar valores de rojo, verde y azul para crear un valor de RGB en particular y en consecuencia un color en especfico. Chip Pearson precisamente aprovech este particular para desarrollar un set de funciones de gran utilidad que emplean como argumento el color de fondo de una celda o de un rango de estas. Lo primero que debemos realizar para conocer cual es nuestra paleta de colores predeterminada es aplicar un simple cdigo para que la misma se muestre en una hoja de clculo. La paleta de colores

consta de 56 colores RGB que MS Excel soporta para su uso por ejemplo en los fondos de celdas y colores de fuentes. El valor de estos 56 colores puede ser cualuiera de entre los 16 millones de colores disponibles pero en la prctica en MS Excel solo podemos emplear 56 colores al mismo tiempo. El cdigo a aplicar para conocer nuestra paleta de colores es el siguiente:Sub Displaypalette() Dim N As Long For N = 1 To 56 Cells(N, 1).Interior.ColorIndex = N Next N End Sub

Este cdigo cambia el color de fondo de las primeras 56 celdas de la hoja activa donde lo ejecutemos. El nmero de fila ser igual a un nmero que llamaremos ndice de colores, que no es ms en que la posicin se encuentra un color de los que sern desplegados de la fila 1 a la 56. Para aquellos que dan sus primeros pasos en VBA recuerden que para poder ejecutar este cdigo tan solo es necesario insertar un modulo nuevo en Microsoft Visual Basic en donde vamos a cargar todos los cdigos con los que vamos a trabajar en esta publicacin. Una vez cargado este cdigo lo ejecutamos desde el panel de Microsoft Visual Basic o bien desde la Cinta de Opciones en la ficha Programador/ Macros y con esto las primeras 56 celdas van a tener los primeros 56 colores de los 16 millones de colores disponibles.

Despus de haberse ejecutado la macro el resultado es tal cual lo mostrado en la imagen anexa:

Esta paleta de colores nos va a servir mediante la funcin de color (ColorIndexOfOneCell) para determinar un valor asociado que a su vez nos va a permitir crear la prueba lgica con la funcin SI.A continuacin, para alcanzar nuestro objetivo inicial vamos a cargar el siguiente cdigo de Visual Basic que nos permitir crear una nueva funcin ColorIndexOfOneCell la cual partiendo de simples argumentos identifica cual es el color de fondo de una celda para arrojarnos como resultado cual es el valor de ndice de color (1 al 56) del fondo de esta:Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _ DefaultColorIndex As Long) As Long ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ColorIndexOfOneCell ' This returns the ColorIndex of the cell referenced by Cell. ' If Cell refers to more than one cell, only Cell(1,1) is ' tested. If OfText True, the ColorIndex of the Font property is ' returned. If OfText is False, the ColorIndex of the Interior ' property is returned. If DefaultColorIndex is >= 0, this ' value is returned if the ColorIndex is either xlColorIndexNone ' or xlColorIndexAutomatic. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim CI As Long Application.Volatile True If OfText = True Then CI = Cell(1, 1).Font.ColorIndex Else

CI = Cell(1, 1).Interior.ColorIndex End If If CI < 0 Then If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then CI = DefaultColorIndex Else CI = -1 End If End If ColorIndexOfOneCell = CI End Function Private Function IsValidColorIndex(ColorIndex As Long) As Boolean Select Case ColorIndex Case 1 To 56 IsValidColorIndex = True Case xlColorIndexAutomatic, xlColorIndexNone IsValidColorIndex = True Case Else IsValidColorIndex = False End Select End Function

Esta funcin est compuesta por los siguientes argumentos: =ColorIndexOfOneCell(Cell; OfText; DefaultColorIndex)

Cell: es la celda cuyo color se debe leer.OfText: son los argumentos lgicos Verdadero o Falso que indican si se devuelve el ndice de color de la fuente (OfText = Verdadero) o el fondo (OfText = Falso).DefaultColorIndex: El parmetro DefaultColorIndex es un valor de ndice de color (1 a 56) que va a ser devuelto si no hay color especfico donde se ha asignado a la fuente o el fondo del relleno. La funcin ColorIndexOfOneCell ofrece una utilidad limitada ya que solo hace referencia a una celda en particular para otros casos tambin Chip Pearson desarrollo funciones adicionales con diferentes caractersticas que gustosamente pueden ver en su publicacin Color Functions In Excel.Ahora vamos a probar que la funcin ColorIndexOfOneCell est trabajando sin presentar ningn inconveniente, los resultados inciales los vemos mediante la siguiente imagen:

En efecto tal como vemos est funcionando correctamente ya que el color amarillo est ubicado en la celda A6 por lo que el valor asociado a este es 6, ahora podemos trabajarla en conjunto con la funcin SI. Tras haber realizado estos pasos ahora vamos a utilizar la funcin ColorIndexOfOneCell como prueba lgica de la funcin SI (como recomendacin previamente se debe tener identificado cual es el valor asociado al color de la celda). En el siguiente ejemplo tenemos una tabla con los resultados de las calificaciones de un grupo de estudiantes con la condicin de que para aprobar la materia la nota debe ser superior a 10 puntos sino la celda se va a colorear de rojo, para esto con anterioridad se seleccionaron las celdas donde estn las calificaciones y se aplico Formato Condicional con la Regla Es menor que colocando que si el valor es menor a 10 puntos el fondo pasa a ser rojo.

Pues solo nos queda aplicar la funcin SI colocando como prueba lgica la funcin ColorIndexOfOneCell que nos va a arrojar el valor 3 correspondiente al color rojo y si lo asociamos con el resto de los argumentos (valor_si_verdadero) las notas que son inferiores a 10 puntos el mensaje que se va reflejar es Reprob, (valor_si_falso) si son mayores el mensaje es Aprob.El resultado de esta operacin es como lo mostrado en la siguiente imagen anexa:

Para obtener este resultado previamente se deben hacer algunas modificaciones que usualmente no realizamos sin embargo de acuerdo a nuestras necesidades nos ofrece una excelente alternativa a una carencia de MS Excel.Les dejamos un archivo MS Excel 2007 habilitado para macros con los pasos citados y el ejemplo propuesto en esta publicacin. Tienen algn otro uso que se le pueda dar a esta interesante funcin? Pues les recordamos que est disponible nuestro foro para responder a sus dudas y debatir sus propuestas. Espero la publicacin les sea de mucha utilidad. Saludos y xito! El Equipo de EXCELLENTIAS.COMFuente: Color Functions In Excel de Chip Pearson, Pearson Software Consulting, LLC.

Artculos Relacionados:

Funcion Excel SI.ERROR

Como hacer un ranking en Excel

Convertir numeros a letras en Excel

Visualizacion: Uso del Color III

Funcion Excel JERARQUIA

About the author

dcedenoMiembro fundador del Equipo EXCELLENTIAS.COM, Daniel es un apasionado emprendedor que busca innovar en el uso de MS Excel aplicado a la visualizacin de informacin y el empleo o desarrollo de nuevas herramientas que potencien la cognicin humana. Quieres conocer ms de Daniel? Sguelo en su Twitter (@d_cedeno ), sguelo en Google+ (Daniel Cedeo Urbina ) o envale un correo electrnico (dcedeno@excellentias.com)! Visit Authors Website Si te gust este artculo, comprtelo! Tagged with: Funcion Color VBA Macro Excel

10 Responses to Prueba logica con color de fondo de una celda1. FELIPE dice: 03/11/2010 a las 5:01 PM Que bueno y que til sta explicacin. Sin embargo, necesito que en vez de color de fondo, pueda usar la funcin SI para el color de la fuente, para identificar los festivos en de un calendario. (En la ficha DATOS -> DESDE WEB, vincul los calendarios desde 2.010 hasta 2.020 y necesito, que a partir del color rojo de los Domingos y festivos, pueda realizar: =SI(A1=COLOR_ROJO;"FESTIVO";NORMAL) y luego usara la funcin =CONCATENAR(A1;"-";CELDA) Agradezco de antemanos su valiosa ayuda. Respondero

dcedeno dice: 04/11/2010 a las 6:11 AM Hola Felipe, bienvenido al equipo.

Puedes emplear la funcin "ColorIndexOfOneCell", detallada en este artculo, para detectar el color de la fuente empleada en una celda. Si bien no detallamos ms este particular en el artculo es perfectamente factible con tan solo cambiar uno de los argumentos. En nuestro ejemplo original el segundo argumento que empleamos lo establecimos en FALSO; para lo que deseas tan solo es necesario cambiar este argumento a VERDADERO. Te dejo anexo un ejemplo de su aplicacin para que lo descargues y puedas verificar en detalle como emplearla: http://excellentias.com/wp-content/uploads/2010/1 Espero que te sea de mxima utilidad, Saludos y xito, Responder

Felipe Serrano g dice: 06/11/2010 a las 6:26 AM Pues lo he probado y funciona de maravilla. Me ahorrar un tiempo valioso. Quedo muy agradecido por el inters y la rapidez en la respuesta. Un clido saludo desde Colombia ! Responder

dcedeno dice: 07/11/2010 a las 1:39 PM Gracias a ti por tu consulta, como siempre estamos a tu servicio. Responder

2.

Patricia dice: 27/12/2010 a las 11:36 AM Hola dcedeno; Tengo una consulta al hacer tu ejercicio los valores todos me quedan en aprobado y no me cambia el color de el texto, quisiera saber como hago para cambiar el color de fondo si el valor es aprobado o reprobadoGracias

Respondero

jmaterano dice: 27/12/2010 a las 9:44 PM Hola Patricia Cmo ests? Empleando la funcin ColorIndexOfOneCell como prueba lgica de la funcin SI obtenemos un valor que esta asociado al valor que coincide con el argumento Cell en este caso es 3 que corresponde al color rojo, el resto son los argumentos de acuerdo al resultado de la prueba logica, si es verdadero Reprob y si es falso Aprob. Aplicando adicionalmente formato condicional sobre los resultados de la formula configuramos para que cuando de como resultado Aprob se refleje el resultado en letras verdes y para cuando el resultado sea Reprob el fondo se refleje en rojo. Anexamos archivo con la solucin propuesta a tu consulta: http://excellentias.com/wpcontent/uploads/2010/12/Funct_Col_Index_Text_Color.zip Esperamos te sea de mucha utilidad, cualquier detalle adicional no dudes en contactarnos. Saludos, Responder

3.

Manuel dice: 15/07/2011 a las 4:12 PM Es una aportacin interesante y es aprendizaje, creo que quiza el caso de Felipe me parece ms prctico, para el anterior, creo que se puede utilizar algo ms simple, unicamente con si por ejemplo, en la 1er celda: =SI(E3>=10,Aprob,Reprob) el resultado es el mismo En lo personal estoy de acuerdo con lo que se dice casi al final: Tienen algn otro uso que se le pueda dar a esta interesante funcin? Pues les recordamos que est disponible nuestro foro para responder a sus dudas y debatir sus propuestas. Gracias

Respondero

dcedeno dice: 16/07/2011 a las 4:59 PM Estoy de acuerdo, hay soluciones mucho mas sencillas partiendo de emplear pruebas lgicas simples. Este en realidad es un ejemplo de todo lo que podemos hacer con Excel cuando empleamos VBA. Las posibilidades son infinitas o al menos el lmite lo pone la imaginacin! Responder

4.

Ilse dice: 04/09/2011 a las 2:08 PM Hola! gracias por tus valiosas aportaciones! Tengo una duda, se puede hacer que por ejemplo si el valor de tal celda es 45, otra celda se ponga gris? y si el valor es por ejemplo 60, ahora otra celda sea la que se ponga gris? Ojala me de a explicar y me puedan ayudar. Respondero

dcedeno dice: 06/09/2011 a las 2:15 AM Hola Ilse, en efecto es posible hacerlo, y es muy sencillo aplicarlo. Supongamos que quiero hacer que la celda B2 de mi hoja de clculo cambie de color a gris si el valor de la celda A2 es 45. Para ello solo debemos aplicar un formato condicional a la celda B2. En Excel 2007 nos posamos sobre la celda B2 y en la ficha Inicio ubicamos el apartado Estilos donde encontrars el botn Formato condicional. Le damos clic y seleccionamos entre las opciones disponibles Resaltar reglas de celdas > Es igual a En el cuadro de dilogo es Igual a aplicamos la frmula: =SI(A245;VERDADERO;FALSO) . Ahora antes de salir de este cuadro de dilogo en la lista desplegable que nos aparece en este mismo cuadro procedemos a definir el formato seleccionando la opcin Formato personalizado. Se desplegar un nuevo cuadro de dilogo llamado Formato de celdas en donde en la ficha relleno

podrs definir fcilmente el color de fondo que quieres se aplique a la celda B2 cuando el valor de A2 sea 45, en nuestro ejemplo gris. Para finalizar haces clic en Aceptar y nuevamente en Aceptar y listo Espero que te haya resultado de utilidad. No dudes en avisarme si necesitas cualquier apoyo adicional al respecto. Un abrazo y mucho xito en todos tus proyectos. Responder