Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is it possible apply Conditional formatting based on the value of a cell from the left?

What I need is to apply a red colour if the value is less then previous cell no colour if it unchanged and green if it is higher.

|  5   |   10  |  8  |   8   |
|      | green | red |       |
share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

This is one of the more basic things with conditional formatting. Click Conditional Formatting on the Home tab, Highlight Cells Rules, Greater Than and either select a cell to compare it to or type the cell reference in. Repeat for less than.


It's a little more complicated to apply such a rule across a row/block.

I'll be using the first row as an example.

  1. Select a block. The 'active cell' is the white cell in the selection. This is important later.

    Screenshot of selected block
    Click for full size

  2. Click Conditional Formatting on the Home tab, Highlight Cells Rules, Greater Than

    Screenshot of menu
    Click for full size

  3. Select the cell you want to compare the active cell to. The other highlighted cells will automagically be compared to the cell shifted according to the relative position to the active cell. In this example, the selected cell is one column to the left of the active cell, so each cell in your selection will be compared to the cell one column to the left of the cell to be formatted. (I'm bad at explaining that, feel free to comment asking for clarification.)

    Select the desired colour.

    Make sure the formula has no dollar ($) signs. They mean it is an absolute reference, which means all cells in the selection will be compared to the specified cell, not the relative one "one column left". We don't want that, we want a relative reference instead, so remove the dollar signs.

    Screenshot of popup with options
    Click for full size

  4. Press OK.

  5. Go back to step 2, but use Less Than this time.

  6. There is no need to set a colour for "equal value", since default is blank. If you wish to set a different colour, there's an Equal To option too.

End result:

Screenshot of formatted cells
Click for full size

share|improve this answer
    
Great, this is exactly what I needed!!! Thanks. –  Nafas Apr 29 '12 at 8:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.