Stop and read this entire post, as if you just cut and paste, it will wreck your current sheet, altering cells! DEPLOY AT YOUR RISK, OR DEPLOY on A COPY!
In the function CalcRow, make certain that the pCell(payment cell) is the correct Column.
In the function CalcRow, make certain that the bCell(balance cell) is the correct Column.
In the function CalcRow, make certain that the cCell(calculated cell) is the correct Column.
The below code starts by running StartCalc.
StartCalc loads a vriable called SheetNumMax with a call to the function counttabs. Count tabs counts and returns the number of sheets in the workbook.
StartCalc then loops through each sheet, and calls a function named FindLastRow, to see how many rows there are on the current sheet.
FindLastRow initializes a variable of rownum, and we loop though each row with the variable "t".
During the loop, we call CalcRow, to perform the claculations of Outstanding balances.
CalcRow also calls for checking the value of cCell, by calling CheckVal and passing cCell.
CheckVal looks at the value of cCell, and if it is greater than 0, it colors the cell yellow.
SheetNumMax = counttabs
For st = 1 To SheetNumMax
rownum = FindLastRow(st)
For t = 2 To rownum
numTabs = ThisWorkbook.Worksheets.Count
counttabs = numTabs
FindLastRow = Cells(ThisWorkbook.Worksheets(OnWhatsheet).Rows.Count, 1).End(xlUp).Row
Dim bCell, pCell, cCell
pCell = "A" & whatrow
bCell = "B" & whatrow
cCell = "C" & whatrow
theval = Range(bCell).Value - Range(pCell).Value
MsgBox (theval) 'remark out this line if you do not wish to have feedback of totals!
Range(cCell).Value = theval
If Range(whatcell).Value > 0 Then
Range(whatcell).Interior.ColorIndex = 6 'yellow