+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting Q: Color Row if certain cells within row add to cell value

  1. #1
    Registered User
    Join Date
    02-25-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    11

    Conditional Formatting Q: Color Row if certain cells within row add to cell value

    As the subject states, I'd like to color a row if the sum of certain cells within that row equal the value of another cell in the same row. In my scenario, I want, for each row considered, the following:

    If the sum of E20, G20, I20, K20 & M20 equals B20, then color the text red. This can only happen if B20 is not blank. If B20 is blank, I don't want the rule applied. If the value of B20 is $0, the rule should apply, as there may be times when that line item was done for free.

    In the attached file, I am formatting the rows based on there being a "Y" in adjacent cells, showing that a payment has been made. However, I can split that payment into two or three by inputting a decimal value instead of "y." (i.e. across the row, if I enter .5 twice, it will add up to one whole payment). When I do this, while the full payment HAS been made, the row is not colored since there is no "y" saying it's paid in full. If I enter Y in an adjacent cell, it will add another payment, which is where the problem arises.

    I can't seem to get the conditional formatting formula right.

    Please lend a hand!

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,604

    Re: Conditional Formatting Q: Color Row if certain cells within row add to cell value

    Corrected formula:
    =$E19+$G19+$I19+$K19+$M19=$B19
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    02-25-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Conditional Formatting Q: Color Row if certain cells within row add to cell value

    Thanks Ben. Now, is there a way to clear cells of data if a cell in that same row is blank/null? I.e. if B20 is blank, then cells E/G/I/K/M20 should also be blank (instead of 0, as the cell is set as default)

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Conditional Formatting Q: Color Row if certain cells within row add to cell value

    Maybe..

    =IF(B20="","",your_formula)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1