Selected Answer
Sorry to say, Khalil, that your logic can't be implemented in the real world. This is because you expect the balance to go to zero sooner or later, and that would signify a new start. Trouble is that it will seldom go to zero, especially with your best customers. Take Sameer in your sample workbook. He paid Rs 15,000 on July 4. What if he would have bought something else on that day and then still paid Rs 11,380 on July 19. The balance wouldn't have been zero and your idea of excluding paid invoices would fail. To be clear: it would fail for your most important and most active accounts while working for less important customers.
The only way to know which invoices are paid is to assign each payment to a particular invoice and then mark invoices that are paid as "settled". Only if you have such a system can we exclude those invoices and the related payments from the display. In your worksheet you have the columns to do that but not the logic needed to fill them.
Part of that logic is your handling of initial balances. In your example, the opening balance of Rs 100 should be booked into the Credit column. This is necessary so that the total of Debit minus total of Credit = Current Balance. The Balance column's first line is calculated same as all the others: E5 + C6 - D6. However, this will fail because E5 is a word, not a number. Beat the problem by using the SUM function.
[E6] =SUM(E5,C6,-D6)
Apply this Custom cell format to E6:E11.
#,##0.00 "DR";[Red]#,##0.00 "CR";
Of course, you can set the formula by code but it doesn't make much sense to ask VBA to ask Excel to do the calculations. VBA knows how to calculate itself. So, once your b/f balance is in the correct column it can draw the correct balance on every line and fill it in. It can also set the cell format.
Your column F should be hidden and it should contain the open balance of each invoice. When a payment is made the balance is reduced, oldest one first. When an invoice is settled the balance for it is zero and it can be excluded from the display.
Your column G is superfluous. The "Get Data" button is also useless. Instead, use the event procedure you already have. Below is the corrected version of this procedure. Just expand the function to not only delete old data but also display the new whenever the user changes the customer name.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B1:C3"), Target) Is Nothing Then
' if a search value changed, clear the data
Sheets("account").Range("A6:H114").ClearContents
End If
End Sub
Note that the range B1:C3 in the above procedure is wrong. Clearly, you mean B1 or C3. However, C3 is totally wrong and should be removed. This balance comes from Sheet1 and can't be modified in the display.
A6:H114 will only work until it doesn't work and then leave you baffled. Learn how to define a dynamic range. Here at Teach Excel we will teach you. Just remember to ask single questions. In this post I am giving you 3 or 4 answers to questions you didn't ask. That's not the way this forum should work. Thank you for your support.