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.
<tbody>
</tbody>
I want to mention too that this forum is my go to for all excel related questions / ideas and solutions!
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º Cliente | Cliente | Fec Ftra | Fecha Vto | Importe Or |
| 10145 | ohsa | 2/19/2013 | 6/19/2013 | 677.38 |
| 10145 | ohsa | 2/19/2013 | 6/19/2013 | 15,452.88 |
| 10445 | STARTER | 11/7/2012 | 3/7/2013 | 707.88 |
| 31468 | AT | 4/29/2013 | 5/29/2013 | 5,060.00 |
| 11111 | ab - bat | 6/12/2013 | 7/12/2013 | 14,220.00 |
| 11112 | ab - oha | 5/20/2013 | 6/20/2013 | 11,376.00 |
| 11113 | ab - mat | 5/15/2013 | 6/15/2013 | 2,688.00 |
| 31496 | DEM | 6/19/2013 | 7/2/2013 | 33,003.00 |
| 31503 | FAL | 5/31/2013 | 6/30/2013 | 1,611.04 |
| 33566 | HBFL | 6/19/2013 | 7/19/2013 | 7,739.28 |
| 31513 | HB | 6/17/2013 | 7/17/2013 | 6,234.48 |
| 21111 | BGL - A | 6/10/2013 | 7/10/2013 | 5,577.12 |
| 21112 | BGL - B | 6/5/2013 | 7/5/2013 | 6,381.36 |
| 21113 | BGL - C | 6/4/2013 | 7/4/2013 | 4,826.16 |
| 31513 | HB | 5/31/2013 | 6/30/2013 | 3,384.48 |
| 31513 | HB | 5/29/2013 | 6/29/2013 | 7,553.28 |
| 31513 | HB | 5/28/2013 | 6/28/2013 | 5,016.24 |
| 31513 | HB | 5/28/2013 | 6/28/2013 | 6,407.04 |
| 31513 | HB | 5/28/2013 | 6/28/2013 | 13,804.32 |
| 31513 | HB | 5/24/2013 | 6/24/2013 | 8,469.12 |
| 31513 | HB | 5/21/2013 | 6/21/2013 | 6,234.48 |
| 31517 | ING | 5/7/2013 | 6/7/2013 | 3,696.00 |
| 31522 | KT | 10/31/2011 | 12/31/2011 | 37,394.50 |
| 31527 | NDS | 10/31/2010 | 12/31/2010 | 1,830.00 |
| 31529 | OHFD | 5/29/2013 | 6/29/2013 | 1,012.50 |
<tbody>
</tbody>
I want to mention too that this forum is my go to for all excel related questions / ideas and solutions!
