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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,095,956
Messages
5,447,556
Members
405,456
Latest member
melmelseh

This Week's Hot Topics

Top