Help : Looping and Conditional Formating

[anish.ms]

Hi Experts,

I have attached a sample file with some codes and request your help in the following areas -

Sheet("Receivable") - To populate the total bill amount against client names in respective months from Sheet('Tracker")
And conditional formatting of the total amount based on the collection amount. Fill in green if completely collected, no color if completely pending and in case of part collection, fill the collection % in green.
I have given the expected results in sheet("Example")

Thanks in advance!

[p45cal]

Select B5:Y6 of the Receivable sheet, starting your selection from B5, then type this into the formula bar:
=SUMIFS(Tracker[Bill Amount],Tracker[Client Name],$A5,Tracker[Billing Month],B$4)
then hold down the Ctrl key while pressing the Enter key. This will fill all the cells with the formula.
(Don't Copy across and down)

In the macro Do_It there's a line which clears all those new formulae!:
.ClearContents
Either disable it, or perhaps replace it with:

.FormulaR1C1 = "=SUMIFS(Tracker[Bill Amount],Tracker[Client Name],RC1,Tracker[Billing Month],R4C)"

[anish.ms]

Thanks a lot p45cal,
I replaced the .ClearContents with your Formula.
How the formula can be modified to consider the amount even if the day in the billing month is not 1st, lets say user has entered 10-Oct-2021 or 31-Oct-2021?
can you help me with the conditional formatting as well?
Thanks in advance!

[p45cal]

If you're talking about the Billing Month column still then:

.Formula2R1C1 = "=SUMPRODUCT(Tracker[Bill Amount],(Tracker[Client Name]=RC1)*(MONTH(Tracker[Billing Month])=MONTH(R4C))*(YEAR(Tracker[Billing Month])=YEAR(R4C)))"
but if it's the Bill Date column then:

.Formula2R1C1 = "=SUMPRODUCT(Tracker[Bill Amount],(Tracker[Client Name]=RC1)*(YEAR(Tracker[Bill Date])=YEAR(R4C))*(MONTH(Tracker[Bill Date])=MONTH(R4C)))"

[anish.ms]

Thanks a lot p45cal,
can you help me with the conditional formatting?
Thanks in Advance!

[p45cal]

Well, there is a solution in the attached but I wouldn't recommend using it.
Your Example sheet conditional formatting (CF) works for 2 reasons: (1)there was CF for each separate cell and (2)the references in the formula for the max amount in the CF had absolute addresses (relative refs aren't allowed).
In the Do_It macro I've put a CF for each individual cell (no CF if there's nothing been collected) and calculated the Max amount for each cell's own CF.
This means that if there are a lot of clients with data for lots of months, the CF on the sheet could get resource hungry and slow down operations.
I've used the Billing Month (not the Bill Date) for both the amounts and the CF.
I haven't tested thoroughly and don't know what will happen as the number of clients decrease (I suspect the CFs won't be deleted on empty rows).

[anish.ms]

Thanks a Ton p45cal

[Krampaul82]

Wow my first thred and i sit humbled,,,,Again.
Well, there is a solution in the attached but I wouldn't recommend using it.
Your Example sheet conditional formatting (CF) works for 2 reasons: (1)there was CF for each separate cell and (2)the references in the formula for the max amount in the CF had absolute addresses (relative refs aren't allowed).
In the Do_It macro I've put a CF for each individual cell (no CF if there's nothing been collected) and calculated the Max amount for each cell's own CF.
This means that if there are a lot of clients with data for lots of months, the CF on the sheet could get resource hungry and slow down operations.
I've used the Billing Month (not the Bill Date) for both the amounts and the CF.
I haven't tested thoroughly and don't know what will happen as the number of clients decrease (I suspect the CFs won't be deleted on empty rows).