If statement / Command Button / Message Box

[N. McCain]

I have an invoice tracking sheet. I need to keep up with what customers have
not paid within 30, 60, and 90 days. I can auto filter and search for them
myself, but I would like to have a command button that would check today's
date in cell "L1", see if it is greater than all the dates in cells F3:F4000.
If today's date is greater than any of those dates, I would like to have a
message box that pops up and says "You have invoices that are 30 days past
due." I am reall lost. I have not done any VB programming since college.
Will someone please help me on this?

 

[N. McCain]

I forgot to mention that there is a fomula in cells F3:F4000 that calculates
a date 30 days after the invoice was billed. I hope it can still be done.

 

[Rick Rothstein \(MVP - VB\)]

Consider this alternative to what you asked for and see if it will work for
you. Select the range F3:F4000 (you can do that quickly by entering F3:F4000
into the Name Box... it is to the left of the formula bar). Once selected,
click Format/Conditional Formatting in Excel's menu bar and do the following
in the dialog box that appears.

First, click the Add>> button two times so that three total conditions are
showing. For each condition, select "Formula Is" in their first drop-down
boxes. Now, click in the second field for Condition 1 and copy/paste this
formula there...

=AND(F3<>"",F3<(TODAY()-90))

Now click the Format button for Condition 1 and click on the Patterns tab on
the dialog box that appears. Select a color... I'd suggest the pale red one)
and then click OK to get back to the Conditional Formatting dialog. Now
repeat the process for the other two conditions using this formula in
Condition 2...

=AND(F3<>"",F3<(TODAY()-60))

and this formula in Condition 3....

=AND(F3<>"",F3<(TODAY()-30))

My suggestion for colors are pale blue for Condition 2 and pale yellow for
Condition 3. When you are through, OK your way back to the worksheet. Any
dates more than 90 days old will be highlighted in pale red; more than 60
day, but less than or equal to 90 days, in pale blue; and more than 30 days,
but less than or equal to 60 days, in pale yellow. This way, you can
immediately identify the status of your accounts by their color (or lack of
it). Note, by the way, that I did not need to use your "today's date" in
L1... Excel has the TODAY() function which generates the current date
automatically.

Rick

 

[N. McCain]

Rick,

Thank you for the help. This is going to work great. I have one more
question for you. I have a column that is called "Posted Date". After the
customer pays the invoice, I put the date they paid it and then it calculates
my incentive. Now, I need a "formula" that will remove the color after the
invoice is paid. Thanks.

 

[Rick Rothstein \(MVP - VB\)]

You didn't say which column is your "Posted Date" column, so I'll guess at
Column G (if I am wrong, just change the G to whatever is the correct column
letter in the formulas below). Reselect F3:F4000, click Format/Conditional
Formatting and replace the formulas I gave you earlier with these
formulas...

Condition 1: =AND(F3<>"",F3<(TODAY()-90),G3="")

Condition 2: =AND(F3<>"",F3<(TODAY()-60),G3="")

Condition 3: =AND(F3<>"",F3<(TODAY()-30),G3="")

Notice, all I did is add {,G3=""} to the existing formulas. What this does
is if anything (a date, number, letter, anything) is in column G for a given
row in your range, then the color will not appear in the same row in column
F no matter what the date is in column F.

Rick