Consulting

Results 1 to 8 of 8

Thread: Help : Looping and Conditional Formating

  1. #1
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location

    Help : Looping and Conditional Formating

    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!
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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)"
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    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!

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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)))"
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks a lot p45cal,
    can you help me with the conditional formatting?
    Thanks in Advance!

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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).
    Attached Files Attached Files
    Last edited by p45cal; 11-21-2021 at 03:30 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks a Ton p45cal

  8. #8
    Wow my first thred and i sit humbled,,,,Again.
    Quote Originally Posted by p45cal View Post
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •