VBA code needed to automate past due with 4 exceptions

MKen

New Member
Joined
Jul 8, 2011
Messages
8
Thanks in advance for any help. I've been searching the forum for a few days to try and piece together a VBA to pull out invoices that are more than 30 days old, and of the 100s of clients, there are 4 exceptions. I know this is more simple with a pivot table, but the end users on this have very limited excel experience. I've done some conditional formatting for it, and then sending back to the user to distribute. Currently, they are manually calculating the dates and using column row fill to format for distribution.

I'd like the code to calculate the days outstanding based on the date of the run from SAP (typically today's date) and stored in A1. Any invoice over 30 days will have the whole row turn red. There are 4 client exceptions (2 at 45 days AB or BGL in the client name, 1 at 90 days client # 33566 and one where the dates can be ignored completely client 10145 name ohsa). Once the red lines are applied I'd like it to subtotal the open invoice amounts for each client and subtotal the outstanding invoice amounts as well.

6/20/2013
Nº ClienteClienteFec FtraFecha VtoImporte Or
10145ohsa2/19/20136/19/2013677.38
10145ohsa2/19/20136/19/201315,452.88
10445STARTER11/7/20123/7/2013707.88
31468AT4/29/20135/29/20135,060.00
11111ab - bat6/12/20137/12/201314,220.00
11112ab - oha5/20/20136/20/201311,376.00
11113ab - mat5/15/20136/15/20132,688.00
31496DEM6/19/20137/2/201333,003.00
31503FAL5/31/20136/30/20131,611.04
33566HBFL6/19/20137/19/20137,739.28
31513HB6/17/20137/17/20136,234.48
21111BGL - A6/10/20137/10/20135,577.12
21112BGL - B6/5/20137/5/20136,381.36
21113BGL - C6/4/20137/4/20134,826.16
31513HB5/31/20136/30/20133,384.48
31513HB5/29/20136/29/20137,553.28
31513HB5/28/20136/28/20135,016.24
31513HB5/28/20136/28/20136,407.04
31513HB5/28/20136/28/201313,804.32
31513HB5/24/20136/24/20138,469.12
31513HB5/21/20136/21/20136,234.48
31517ING5/7/20136/7/20133,696.00
31522KT10/31/201112/31/201137,394.50
31527NDS10/31/201012/31/20101,830.00
31529OHFD5/29/20136/29/20131,012.50

<tbody>
</tbody>

I want to mention too that this forum is my go to for all excel related questions / ideas and solutions!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,257
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top