Share via

Conditional formatting compare two columns check values highlight cells

Anonymous
2012-05-02T11:34:57+00:00

The spreadsheet has about 1000 rows of data. What I am trying to do is to apply conditional formatting to column b  so that excel checks the values in that column and compares them to the values in column D and where the cell value  in Column D is higher than the cell in the corresponding row in  column E, i want the formatting to highlight the cell

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2012-05-02T11:55:57+00:00

    Select column B (or the part that you want to format conditionally).

    Let's say that B1 is the first cell in the selection.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula =D1>E1

    (If the first cell in the selection is B3 instead of B1, use =D3>E3, etc.)

    Click Format... and specify the desired formatting.

    Click OK twice.

    50+ people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-08-04T13:59:12+00:00

    Hi,

    Try this

    1. Click on cell O2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
    2. Type =N2>O2
    3. Click on Format > Font > Red > OK
    4. Click on Apply
    5. Click on O2, select O3:O50, right click > Paste Special > Formats > OK

    Hope this helps.

    This does not work... it keeps comparing the values to O2 as if it was $O$2

    10+ people found this answer helpful.
    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2015-02-05T03:40:45+00:00

    Hi,

    Try this

    1. Click on cell O2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
    2. Type =N2>O2
    3. Click on Format > Font > Red > OK
    4. Click on Apply
    5. Click on O2, select O3:O50, right click > Paste Special > Formats > OK

    Hope this helps.

    10+ people found this answer helpful.
    0 comments
  3. Anonymous
    2015-04-18T17:42:13+00:00

    Do the conditional formatting but remove the $ signs in the formula. Copy using Format Painter, your'e good to go.

    Writing in a hurry, ask me if you need more info. Sure to work.

    10+ people found this answer helpful.
    0 comments
  4. Anonymous
    2015-02-05T03:14:01+00:00

    I attempted the conditional formatting but instead of comparing each row against each other it turned them all red.

    I billed out column N, but received column O for payments. I want to see if N2>O2, N3>O3, etc.

    Please advise.

    6 people found this answer helpful.
    0 comments