Using VBA coding to count color Occurrences

[Danny Boy]

Hi:

I am using Office (Word, Excel, and Powerpoint) 2007, and Windows 7, and
below is what I'm trying to accomplish, hopefully, with VBA coding:

In Columns L & M I have dates that identify a beginning and end date for a
class (L is "beginning" date and M is "end date") that a student has signed
up for. If the student has not paid for their class, the class dates in
Columns L & M are in "bold red font". Once payment has been made, the font
changes from "bold red" to "unbold black" thanks to conditional formatting.
This works just fine. However, I'm trying to accomplish two additional things
that I'm having trouble with, and wasn't sure if VBA coding may be my answer:

Issue 1:

I'd like coding that gives a numerical outcome of how many clients,
scheduled for a class have not yet paid for their class (in other words,
count all bold red font in Column L as this would identify the number of
individuals who haven't paid. If the font is anything but bold red, than this
should not be counted, as the font color automatically turns to unbolded and
black once a client is identified (via a specific date in Column K) as paid.
This numerical outcome (clients who have not yet paid) should appear in cell
K3.

Issue 2:

I'd like coding that merely gives me a flag which states: "Pending Balances"
if the font color in color is Column L is red and bold. This flag should
appear in cell L3.

Thanks very much for any help in advance!

Dan

 

[Rick Rothstein]

Since the color is coming from Conditional Formatting, then you have a
formula relationship that evaluates to TRUE and FALSE (the one you used in
the Conditional Formatting dialog box)... just use that formula relationship
to create your count in K3 and to flag your cells in Column L.

 

[Danny Boy]

Do you have a formula suggestion to make this work Rick? I understand in
theory what you are suggesting, and it makes sense, but I just can't figure
out how to get Excel to count or flag based upon a bold red colored font. I'm
still an Excel Newbie lol!

 

[Rick Rothstein]

It would help if you tell us the formulas being used in the Conditional
Formatting to color the cells red and black.

 

[Danny Boy]

I'm using the formula you see below in Column L. I had preset the font to
Bold and Red (which appears whenever I enter a date in Column L), and with
the Conditional Formatting formula below, the font changes to unbolded black
whenever a date is entered into Collumn K:

=ISNUMBER(K4)