Results 1 to 5 of 5

Conditional formatting based on adjacent cell

This is a discussion on Conditional formatting based on adjacent cell within the Excel Questions forums, part of the Question Forums category; I am using Excel 2007, I need to highlight in RED cells in a pivot table if the value in ...

  1. #1
    New Member
    Join Date
    Nov 2007
    Posts
    19

    Default Conditional formatting based on adjacent cell

    I am using Excel 2007,

    I need to highlight in RED cells in a pivot table if the value in the adjacent cell is less than the referenced cell.

    For any row in the Pivot Table
    If Cell in column C = 0
    and Cell in column D = 1

    Cell in column D should be highlighted in RED.

    I can't seem to figure out how to refer to the adjacent cell in a conditional format formula.

    Can someone help.

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,659

    Default Re: Conditional formatting based on adjacent cell

    Select Column D and use the following formula:

    =AND($C1=0,$D1=1)
    Office 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  3. #3
    New Member
    Join Date
    Nov 2007
    Posts
    19

    Default Re: Conditional formatting based on adjacent cell

    A little clarification, D = 1 and C = 0 were only examples. I need to flag column D whenever it's value is greater than column C, regardless of the values.

    Example:
    D = 5
    C = 2

    D = 0
    C = -1

    Sorry for the confusion.

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,659

    Default Re: Conditional formatting based on adjacent cell

    OK, so use the formula:

    =$D1>$C1
    Office 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  5. #5
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,577

    Default Re: Conditional formatting based on adjacent cell

    I don't know about Excel 2007, but in prior versions, just make the formula suit the cell that is active when you are setting up the conditional format.

    So, if you select range B2:B100 and the single cell that is active is B2, the formula might be =B2>A2

    This (note relative reference) will then apply to all selected cells in the same relative way. You can check after applying it.

    If though cell B10 were active, you'd type in =B10>A10

    HTH

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com