+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting pasting issue when referencing another cell

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    18

    Conditional Formatting pasting issue when referencing another cell

    Hi -

    I have a table of data (Table A) that I am trying to highlight if the formulas into a corresponding table (Table B) are equal to 0 or 1. So when Table B = 1, the corresponding cell in Table A would turn to red.

    I have been trying all day to write the correct formula that would allow me to reference the formula in Table B, to allow for the conditional formatting in Table A. The problem is, when I go to copy the formula to all the other cells in Table A, it references the cell where I first wrote the original formula.

    Can someone please help me understand how to write the correct formula for this conditional formatting? I posted my spreadsheet to show what I am viewing. Please help!!Book2.xlsx

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Conditional Formatting pasting issue when referencing another cell

    Select cells B8:K27 (make sure B8 is the active cell) and apply the following formula in conditional formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    - Vince

    If you want the whole row to be highlighted, instead of just the cell with the dollar amount, change the formula to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting pasting issue when referencing another cell

    just to add to what Moo suggested, use "use formula" when you enter his formula
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    10-19-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Conditional Formatting pasting issue when referencing another cell

    Thanks Moo and Fdibbins! Just so I understand, how does this formula actually work? I have not used the AND and ISNUMBER functions in my formulas before.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting pasting issue when referencing another cell

    and() and or() test for multiple conditions to be TRUE. with AND, all conditions must be met, for OR, at least 1 must be bet.

    AND(if the sun shines,the wind doesnt blow,there is gas in the tank,dad wants to drive)...we can go to the beach
    OR(we have meat at home, we have rice at home)...we can eat

    ISNUMBER() checks to see if the cell contents are a number or text. so ISNUMBER(B8) is testing to see if there is a number in B8

    hope that helps?

+ 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