sumar y contar celdas por color de formato condicional

7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional

http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 1/13

Sumar y contar celdas

por color de formatocondicionalLas funciones creadas evalúan la propiedad Interior.Color que tiene

cada una de las celdas en Excel, sin embargo, el formato condicional

no utiliza dicha propiedad sino que tiene su propia “versión” para

almacenar el color de fondo de una celda. or esa razón no es posibleutilizar dichas funciones para evaluar colores establecidos a trav!s de

una regla de formato condicional.

la función "#$ %&'() para sumar y contar celdas por color de

formato condicional. Los ob*etos + propiedades que debemos

evaluar para obtener el color de fondo proveniente de un regla de

formato condicional.

La colección FormatConditions

rimero debemos recordar que un mismo rango de celdas en Excel

puede estar su*eto a varias reglas de formato condicional al mismo

tiempo, as que para guardar esa lista de formatos condicionales se

creó la colección FormatConditions en "#$ la cual enumera todas las

reglas de formato condicional aplicadas en un rango.

ara comprender el funcionamiento de esta colección haremos une*emplo. -onsidera la siguiente lista de números en el rango $/$0

donde he aplicado una regla de formato condicional que resalta en

color ro*o los valores ma+ores a 120.



$hora insertar! un botón de comando $ctive3 en la misma ho*a +

colocar! el siguiente código "#$ en su evento -lic4/

5

6

Private Sub CommandButton1_Click()

MsgBox Range("A1A1!")#ormatConditionsCount

$nd Sub

La única lnea de código e*ecutada se encargar7 de mostrar el valor de

la propiedad Count de la colección FormatConditions la cual contiene el

recuento de las reglas de formato condicional para el rango indicado.

$l hacer clic sobre el botón de comando se mostrar7 el siguientemensa*e/

El mensa*e nos indica que el rango $/$0 tiene una sola regla de

formato condicional. $hora crear! una segunda regla para el mismo

rango que resaltar7 de color verde todas las celdas con un valor

menor a 520. &na vez creada la regla de formato condicional, volver!

a pulsar el botón de comando + el número mostrado en el mensa*e

habr7 aumentado debido a la nueva regla creada/



Es as como la colección FormatConditions nos permite obtenerinformación sobre las reglas de formato condicional aplicadas a un

rango de celdas. $hora centraremos nuestra atención a una

propiedad espec8ca de dicha colección.

La propiedad FormatCondition.Interior.Color

-ada regla de formato condicional almacena el estilo que aplicar7 a

las celdas que cumplan con las condiciones establecidas +

espec8camente el color de relleno se almacena en la propiedad

Interior.Color . ara demostrar el valor de esta propiedad agregar! un

nuevo botón de comando con el siguiente código/

5

6

92

:

Private Sub CommandButton%_Click()

#or i & 1 'o Range("A1A1!")#ormatConditionsCount

MsgBox "Regla " i vb* _

"Color "

Range("A1A1!")#ormatConditions(i)+nteriorColor

,ext i

$nd Sub



El código anterior recorre todos los elementos de la colección

FormatConditions + para cada elemento mostrar7 un mensa*e con el

número de regla + su color de relleno que est7 almacenado en la

propiedad Interior.Color . $l pulsar el botón obtengo el siguiente

resultado/

El color devuelto ser7 un valor entre 0 + :11152 que corresponde a

una de las combinaciones de colores primarios %ro*o, verde + azul) que

se pueden formar en Excel.

Los colores mostrados con el código anterior son los colores

pertenecientes a cada una de las reglas de formato condicional + no el

color de una celda espec8ca. ara conocer el color de formato

condicional aplicado a una celda ser7 necesario encontrar la regla que

se cumple sobre dicha celda para entonces obtener el colorcorrespondiente.

Macro para obtener el color de una celda



El desafo m7s grande al crear una macro para obtener el color de

una celda es descubrir la regla de formato condicional que est7 activa.

ara eso utilizamos un bucle (or ;ext que recorrer7 toda la colección

de formatos haciendo una evaluación de cada regla para descubrir si

est7 activa.

5

6

9

2

:

1

<

=

0

5

6

9

2

:

1

<

=

#unction C--R#C(Celda As Range) As ong

.+ndicar/ si la relga de *ormato condicional est/ activa

0im ReglaActiva As Boolean

.Recorrer todas las reglas de *ormato condicional ara la celda

indicada

#or i & 1 'o Celda#ormatConditionsCount

.$valuar la regla #ormatConditions(i)

2it3 Celda#ormatConditions(i)

.Si la regla est/ basada en el valor de la celda

+* '4e & xlCell5alue '3en

.+denti*icar el oerador de la regla 4 evaluar si est/ activa

Select Case -erator

Case xlBet6een ReglaActiva & Celda5alue 7&

$valuate(#ormula1) _

And Celda5alue 8& $valuate(#ormula%)

Case xl,otBet6een ReglaActiva & Celda5alue 8&

$valuate(#ormula1) _

-r Celda5alue 7& $valuate(#ormula%)

Case xl$9ual ReglaActiva & $valuate(#ormula1) &

Celda5alue

Case xl,ot$9ual ReglaActiva & $valuate(#ormula1) 87



5

0

5

55

5

6

5

9

5

2

5:

5

1

5

<

5

=

6

0

6

6

5

6

6

6

96

2

6

:

6

Celda5alue

Case xl:reater ReglaActiva & Celda5alue 7

$valuate(#ormula1)

Case xless ReglaActiva & Celda5alue 8

$valuate(#ormula1)

Case xl:reater$9ual ReglaActiva & Celda5alue 7&

$valuate(#ormula1)

Case xless$9ual ReglaActiva & Celda5alue 8&

$valuate(#ormula1)

$nd Select

.Si la regla es una exresi;n (#;rmula)

$lse+* '4e & xl$xression '3en

AlicationScreen<dating & #alse

CeldaSelect

ReglaActiva & $valuate(#ormula1)

Range(ActiveCellAddress)Select

AlicationScreen<dating & 'rue

$nd +*

.0evolver el color si la regla est/ activa

+* ReglaActiva '3en

C--R#C & +nteriorColor

$xit #unction $nd +*

$nd 2it3

,ext i



1

6

<

6

=9

0

9

9

5

9

69

9

9

2

9

:

9

1

9

<

9

=

$nd #unction

Las primeras lneas de código se encargan de recorrer todas las reglas

de formato condicional para la celda proporcionada. La primera

validación importante es sobre la propiedad Type que nos permite

saber el tipo de regla que estamos analizando, +a sea del tipo xlCellValue o del tipo xlExpression. Es importante mencionar que

existen m7s tipos de reglas de formato condicional, de hecho la

enumeración 3l(ormat-ondition>+pe contiene 9 tipos diferentes,

pero solamente los dos tipos mencionados anteriormente tienen una

manera de ser evaluados desde "#$. or esta razón la función



COLORFC considera en primer lugar las reglas de formato condicional

que evalúan directamente el valor de una celda.

El cuadro de di7logo anterior muestra precisamente la lista de reglas

de formato condicional que son del tipo xlCellValue + que por lo tanto

ser7n reconocidas por nuestra función. $hora hagamos una prueba

con los datos de e*emplo para ver cómo la función COLORFC devuelve

el color aplicado a cada celda de acuerdo a la regla de formato

condicional activa. ?ecuerda que las reglas creadas resaltan de ro*o

las celdas con un valor superior a 120 + de color verde los valores

menores a 520.



;uestra función "#$ funciona correctamente al devolver el código decolor adecuado para cada celda. El otro tipo de reglas consideradas

en la función COLORFC son aquellas basadas en una expresión, que

generalmente son las reglas basadas en una fórmula que contiene

funciones de Excel. ero debo darte una mala noticia, esta opción

solo funcionar7 si tenemos Excel en ingl!s. @ a continuación explico

por qu!.



$l momento de crear una regla de formato condicional que utilice una

función de Excel, colocaremos su nombre en castellano. 'icha

fórmula se almacenar7 en una propiedad de la regla de formato

condicional + al momento de evaluarla desde "#$ la cadena de texto

contendr7 el nombre de la función en espaAol lo cual ocasionar7 unerror +a que "#$ solamente entiende las funciones de Excel por su

nombre en ingl!s. $ún as he de*ado esta funcionalidad porque existe

un porcenta*e de usuarios que utilizan Excel en ingl!s + ellos podr7n

obtener el color de las celdas que utilicen reglas de formato

condicional basadas en fórmulas.

'espu!s de todas estas aclaraciones %que eran necesarias), tenemos

una función que nos devolver7 el color aplicado a una celda desde

una regla de formato condicional, as que ahora solo debemosutilizarla para sumar o contar celdas por color.

Sumar por color de formato condicional



ara sumar por color de formato condicional crear! una nueva

función "#$ de la siguiente manera/

56

9

2

:

1

<

=

0

5

6

9

2

:

1

#unction S<MARP-RC--R#C(CeldaColor As Range= Rango As Range) As

0ouble

0im Celda As Range

0im 'otal As 0ouble

0im Color As ong

Color & C--R#C(CeldaColor)

#or $ac3 Celda +n RangoCells

+* C--R#C(Celda) & Color '3en

'otal & 'otal > Celda5alue

$nd +*

,ext Celda

S<MARP-RC--R#C & 'otal

$nd #unction

El primer argumento de la función es una celda que contiene el colorpor el cual deseamos sumar, as que una de las primeras acciones es

obtener dicho color con la función COLORFC . osteriormente se har7

un recorrido por todo el rango de celdas para sumar el valor de

aquellas que tengan el mismo color. Bbserva cómo traba*a nuestra

función con los datos de e*emplo/



La función devuelve correctamente la suma de las celdas $ + $= que

son las que tienen el mismo color que la celda $ que ha sido

indicada como el primer argumento de la función.

Contar por color de formato condicional

Ci en lugar de sumar deseamos contar las celdas que tienen un

mismo color, ser7 su8ciente con modi8car la función anterior para

que en lugar sumar el valor de la celda se sume el valor por cada

celda con el color indicado. El código de esta nueva función es elsiguiente/

5

6

9

2

:

1

<

=

0

#unction C-,'ARP-RC--R#C(CeldaColor As Range= Rango As Range) As

+nteger

0im Celda As Range

0im 'otal As +nteger

0im Color As ong

Color & C--R#C(CeldaColor)

#or $ac3 Celda +n RangoCells



5

6

9

2

:

1

+* C--R#C(Celda) & Color '3en

'otal & 'otal > 1

$nd +*

,ext Celda

C-,'ARP-RC--R#C & 'otal

$nd #unction

$l utilizar esta función con los mismos datos de e*emplo podr7s

observar que la función CONTARORCOLORFC nos devuelve el valor 5

que corresponde a las celdas con el mismo color que la celda $6/

sumar y contar celdas por color de formato condicional

Documents