Forum Discussion

Eric Reames's avatar
Eric Reames
Copper Contributor
Feb 05, 2018

Pay sheet function

I am working on a balance sheet type of workbook and wondering if there is a function or conditional format that can help me with the following:

 

Trying to set it up if account is showed as paid then it it would automatically highlight the entire row green and if it is not paid that it would it would be red.

 

I am not going to be using it for budget purposes, just as a tracking of the payment status of the accounts.

Formulas & Functions
tips and tricks
  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Feb 05, 2018

    Create 2 Conditional Formatting Rules

     

    Step

    1. Create a name using Name Manger, IsPaidRow 
      = Sheet1!$F2 <> ""

      This is for easier reference in future

    2. Select A2
    3. Click Conditional Formatting
    4. Manage Rule
    5. New Rule
    6. "Use a formula to determine which cells to format"
    7. Type 
      = IsPaidRow
    8. Apply fill background color with green format or any format you want for paid record
    9. Click ok
    10. Still in Conditional Formatting Rule Manager, type the following reference in "Applies to" 
      =$A$2:$G$3
    11. Click New Rule
    12. "Use a formula to determine which cells to format"
    13. Type 
      =Not(IsPaidRow)
    14. Apply fill background color with red format or any format you want for paid record
    15. Click ok
    16. Still in Conditional Formatting Rule Manager, type the following reference in "Applies to" 
      =$A$2:$G$3
    17. Click ok to close the Conditional Formatting Rule Manager

    Check if this fit your need.

     

     

Resources