Excel 2007 - Conditional Formatting - Comparing two cells in the same column?

2
votes

This seems like an awfully simple problem, but I'm having a devil of a time trying to get it to work.

Basically I have a column of numbers that run like this:

  1. 56.69
  2. 56.74
  3. 56.83
  4. 56.78
  5. 56.78
  6. 56.7
  7. 56.61
  8. 56.58
  9. 56.57
  10. 56.58

All I want to do is highlight a cell when it is lower/higher than the one above it. For example, since item #2 in the list above is higher than #1 above, that cell would be highlighted green. Same with #3. #4, since it's lower than #3, would be highlighted red.

I've tried doing formulas like this:

  • Putting in a formula= ($K3 < $K2) // which attempts to isolate the column but not the row reference (K is the row that contains the numbers)

  • Using the "Format Only Cells that Contain" selection and choosing: "Cell Value" "Less Than" "=$K2"

The first bulleted item does color cells, but not correctly. It doesn't seem to be comparing the items quite right (highlights cells red that are clearly greater in value than the previous cell).

I've also attempted to variations on the formula, such as: + K4 > K3 (referencing the cell below the selected (referenced) cell when creating the formula) + Using / not using parenthesis using the formula option.

Thanks in advance for any help and/or advice! Please let me know if I left out any details.

Yours, Spider

2
excel-2007excel-formulahighlightconditional-formatting

2 Answers

2
votes

It's simple enough...

  • Select the second cell
  • Select conditional formatting
  • High Light Cell
  • Less Than
  • In the value select the cell above
  • Now copy the second cell and click format painter
  • select all the cells below and you should be done

Screenshot:

Here is the Screenshot

1
votes

I know this is late but there is simpler way (of doing basically the same thing), assuming this is only one column (for now I'm going to assume A1:A10). You use conditional formatting and "Use a formula to determine which cells to format".

For Red Cells:
Formula: =$A2>$A1
Applies to: =$A$2:$A$10

For Green Cells:
Formula: =$A2 Applies to: =$A$2:$A$10

If you use the "New Rule" selection as opposed to "Manage Rules" highlight the cells (i.e. A2:A10) first and then add that formula.

This won't highlight the first cell as there is no cell above it to determine the formula and won't highlight any cells that are the same value as the cell above it.

The colours would be:
1. None
2. Green
3. Green
4. Red
5. None
6. Red
7. Red
8. Red
9. Red
10. Green