Logic Formula using colors in the formula?

[Danny Boy]

Hi:

I am using Office (Excel) 2007, and for some reason I thought that a change
was made in this version of Excel, that allows for formulas which take into
account the font color. Here is what I'm trying to accomplish:

In Columns L & M I have dates that identify a beginning and end date for a
class that a student has signed up for. If the student hasn't paid in full,
the class dates are in bold red font. Once payment has been made, I change
the font to black (and unbold it). The two issues I'd love to do with formula
(to make things easier) are the following:

Issue 1:

If a date is identified in Column K (this column shows date student paid in
full), the font in columns L & M should automatically change from bold red,
to unbolded black.

Issue 2:

I'd like a formula that does a Countif to tell me how many clients,
scheduled for a class have not yet paid (in other words, count all bold red
font in Column K as this would identify the number of individuals who haven't
paid for their classes yet).

Issue 3:

I'd like a logic formula (using an IF statement I suspect) that merely gives
me a flag which states: "Pending Balances" if the font color in columnss L or
M are red and bold.

Thanks very much for any help in advance!

Dan

 

[Luke M]

1) Assuming your base formatting in L & M is unbolded black, Conditionally
format those cells. (with row 2) Formula is:
=ISNUMBER(K2)
Format to bold, red font

2) Single formula is:
=COUNTBLANK(K2:K100)
or, if the cells in column K are not truly blank:
=COUNTA(K2:K100)-COUNT(K2:K100)

3) Similar to above, formula would be:
=ISBLANK(K2)
or
=NOT(ISNUMBER(K2))

 

[Danny Boy]

Hi Luke!

The first outcome (Issue 1) worked using conditional formatitng. Thanks!
However, the second and third did not, as I'm trying to count only those
occurances that are in bold red with respect to Issues 2 and 3. Not sure if
this can be accomplished through conditional formatting or VBA coding. Any
help is welcome. Thanks in advance............Dan