Conditionally format in code and count at same time

[Guest]

After reading quite a few posts I see there is no way to count the number of
color entries in a worksheet – and have the numbers update “live†and
automatically as coloring changes. This is a problem for my requirements, in
addition to some other functionality that my worksheet requires. Hopefully
someone can take this on!!

What I’m trying to do is to highlight overdue tasks, tasks that are due
within the next seven (preferably working) days, tasks due in 2 weeks time,
and show a summary of these (and thus progress against the project) in a
Red/Green/Yellow table.

The worksheet has a number of cells where the user will enter a dates for
when a task is due and when the task is complete. These date cells have the
“CalcProgress†style applied to them. I want to highlight the due dates in
green and red, and automatically total the number of these coloured entries
in the worksheet and update the totals as they change.

For example:

Total Red Cells: nnn
Total Green Cells: nnn
Total Yellow Cells: nnn

If the due date is <=Today() , the font should be red, bold

If the due date is < =TODAY()+7, the font should be yellow bold

If the due date is < =TODAY()+14, the font should be green (black, not bold)

HOWEVER!! When the user enters a date in the “Date Complete†cell for the
item, due date colouring should revert to plain text (normal). As the “Date
Complete†cells are filled, the total count of red, green and yellow cells
would decrease accordingly.

Hope this is understandable – and possible!!

Any assistance will be really, really appreciated!

 

[Mike Woodhouse]

After reading quite a few posts I see there is no way to count the number of
color entries in a worksheet - and have the numbers update "live" and
automatically as coloring changes. This is a problem for my requirements, in
addition to some other functionality that my worksheet requires. Hopefully
someone can take this on!!

What I'm trying to do is to highlight overdue tasks, tasks that are due
within the next seven (preferably working) days, tasks due in 2 weeks time,
and show a summary of these (and thus progress against the project) in a
Red/Green/Yellow table.

The worksheet has a number of cells where the user will enter a dates for
when a task is due and when the task is complete. These date cells have the
"CalcProgress" style applied to them. I want to highlight the due dates in
green and red, and automatically total the number of these coloured entries
in the worksheet and update the totals as they change.

For example:

Total Red Cells: nnn
Total Green Cells: nnn
Total Yellow Cells: nnn

If the due date is <=Today() , the font should be red, bold

If the due date is < =TODAY()+7, the font should be yellow bold

If the due date is < =TODAY()+14, the font should be green (black, not bold)

HOWEVER!! When the user enters a date in the "Date Complete" cell for the
item, due date colouring should revert to plain text (normal). As the "Date
Complete" cells are filled, the total count of red, green and yellow cells
would decrease accordingly.

Hope this is understandable - and possible!!

Any assistance will be really, really appreciated!

For the automatic colouring, you're probably going to need Conditional
Formatting (on the Format menu). I don't know if this can be attached
to a Style or not... I suggest you use the "Formula Is" option rather
than "Cell Value Is".

While it's fairly easy normally to establish the colour (background or
font) of a cell via a smallish VBA function, it's tricker by far when
the colouring is applied via conditional format. Ticky enough that I
recommend you avoid it, if only because I don't know right now how best
to go about it. (It's likely to involve examining the formatting
conditions in code, working out what they think about things and what
the result would be - as I say, tricky).

Fortunately, we don't have to count cells by colour, we can just count
the numbers that meet our formatting conditions. There's COUNTIF(),
which I don't like much (can be slow and I hate putting formula-type
stuff in quoted text) and then there are array formulae, which I
perhaps like too much...

Say your due dates are in cells B2:B99, then to count the dates that
are within 7 days from now, enter the following:

=SUM(IF(B2:B99-TODAY()<7,1,0))

....and enter it into the spreadsheet using Control+Shift+Enter (hold
down Control and Shift before pressing Enter) which creates an array
formula. The yellow and green counts are left as an exercise for the
student...

HTH,

Mike

 

[Guest]

Hi Mike. I'm afraid that didn't work. The only thing I changed was to put in
some of the actual cell references in my worksheet...
=SUM(IF(F22:F26-TODAY()<7,1,0))

Also, since I don't actually want to sum the date cells - only get the
number of cells that meet the criteria, I tried the following:

=COUNTIF(F22:F26,"<TODAY()")

For the sample data below, the result came out as 0 where it should have
been 2

Col F
Row 22 11-Nov-02
23 11-Nov-03
24 16-Nov-06

Help!

 

[Peter T]

Hi Help,

Try this one

=SUMPRODUCT(--(F23:F27<TODAY()))

but if you particularly want to stick with countif
=COUNTIF(F22:F26,"<"&VALUE(TODAY()))

Regards,
Peter T

 

[Guest]

Thank you Peter - that's what I wanted! Now to figure out how to do the rest!

Cheers,

 

[Guest]

Hi again. I'm having difficulty getting the code Peter gave me so nicely and
hope he or Mike or someone can help again.

In F23 through F27 I have conditional formatting set as follows. This is to
show me any tasks that are overdue, due within the next week, and those that
are due in over 2 weeks.

* Cell value is less than =Today() turn font red, bold
* Cell value is less than =Today()+7 turn font yellow, bold
* Cell value is less than =Today()+14 turn font green

NOTE: Technically, I don't want the fonts colored at all if any cells
between Q23 and Q27 have dates in them! This is because Q23 and Q27 have
completion dates in them.

Anyway, in F23 through F27 the cell values are thus:
11-Nov-05 (formatted bold red)
08-Nov-02 (formatted bold red)
18-Nov-06 (formatted bold yellow)
19-Nov06 (formatted bold yellow)
30-Nov-06 (formatted bold green)

However, when I use the following commands for counting the occurances, it
reseults in the following:

=COUNTIF(F23:F27,"<"&(TODAY())) - results in 2 (okay - should be 2)
=COUNTIF(F23:F27,"<"&(TODAY()+7)) - results in 4 (should be 2)
=COUNTIF(F23:F27,"<"&(TODAY()+14)) - results in 5 (should be 1)

 

[Peter T]

Hello again,

Assuming Today() is date of post (17-Nov-06) your formulas are returning
correct results.

If you apply what are currently your 2nd or 3rd CF's as the 1st I think
you'll see the confusion.

Try these
=SUMPRODUCT(--(F23:F27<TODAY()))
=SUMPRODUCT(--((F23:F27<(TODAY()+7))*(F23:F27>=TODAY())))
=SUMPRODUCT(--((F23:F27<(TODAY()+14))*(F23:F27>=TODAY()+7)))

Alternatively these entered in A1:A3
=SUMPRODUCT(--(F23:F27<TODAY()))
=SUMPRODUCT(--(F23:F27<(TODAY()+7)))-A1
=SUMPRODUCT(--(F24:F28<(TODAY()+14)))-A1-A2

No doubt can be adapted to COUNTIF if you prefer.

Regards,
Peter T

 

[Guest]

I see said the blind man.....er..women! Thanks so very much for your help,
Peter. Seeing your code and putting it into action has helped me understand
what was happending. Your a star!

Cheers,