Thread: Counting colors using VBA

So, after doing much reasearch, I got my sheet to work. I am using VBA to count colors. I am using this:

=ColorFunction(B2,STAFF!D2:STAFF!D61)

This counts all the red cells. B2 is manually filled red (a controll cell). I have dates is colum D2:D61. I am using conditional formating to color them. Such as:

=(DATEDIF(D2,$C$77,"m")/12)>0.95

I have it set to fill the cell red. It works well. If I hit CRL + ALT + F9 it updates the macro if I manually change a cell's fill color. All is well until I try to update the sheet. I am exporting my raw data from MS Access to Excell. The copy and paste into my template with the above formulas. Once I do that the color if fine but the macro no longer works.

What gives?

You don't say where you got the ColorFunction from or what, exactly it does.

In my experience, these functions count true formatted colours, not colours induced by Conditional Formatting.

What are you counting?

Regards

Oh, and you'd need to make the function volatile so that it responds to changes in the sheet and recalculates.

Regards

Do you mean this?

=DATEDIF($D2,$C$77,"y")>=1

If so you could use a helper column, say G, which can be hidden if you want.
In G2

=DATEDIF($D2,$C$77,"y")

Drag/Fill down to G62

Then

=COUNTIF($G$2:$G62,">=1")

This would be far simpler than trying to count cell colours that are applied with conditional formatting, that can become quite complicated.

Hope this helps

An alternative to your function is this one which you may be able to use and then do a countif function.

http://www.datapigtechnologies.com/f...rtonColor.html

Alan

Originally Posted by Wilburt

I have dates is colum D2:D61. I am using conditional formating to color them. Such as:

=(DATEDIF(D2,$C$77,"m")/12)>0.95

I have it set to fill the cell red.

As inferred by others - if you have logic determining the colour then ape the logic in your count formula - no need for VBA at all.

=SUMPRODUCT(ISNUMBER(D2:D61)*(DATEDIF(STAFF!D2:D61,$C$77,"m")/12>0.95))

we're assuming no text values can be added to the range - if they can then an Array would be preferable

Originally Posted by TMS

...you'd need to make the function volatile so that it responds to changes in the sheet and recalculates.

The precedent ranges are explicit so as they recalculate so too the UDF.

Altering the format of a cell manually (B2) is not a Volatile action so making the UDF volatile would not resolve that issue .. though it would reduce the exposure to error given the UDF would calculate upon the next volatile action irrespective of precedent ranges.

Do you mean > 1 or >= 1 ?

It might be an idea to provide some examples of the dates you felt were not generating the appropriate formatting.

On an aside if you're using XL2007 or above you might also consider use of EDATE to work dates backwards
(pre XL2007 EDATE requires activation of Analysis ToolPak Add-In on each client PC that will use the file)

I attached an example. Not sure of the difference in the = sign. As you can see the first table fails to shade 4 dates that are in the month past a year old. Also, I am using 2007, but some users that will access it might be using the compatibility one (2003-2007)?

Edit to add: The formula in the example above the table should read C1 and D4 not C77 and D2.

Originally Posted by Wilburt

Not sure of the difference in the = sign

>1 means greater than one - in excess of one year

>=1 means greater than or equal to one - one year or more

If you modify the first table to use >= rather than > you will get the same results as the second table.