I have encountered numerous questions related to changing a cell color based on its value, which can be achieve easily with Excel "Conditional Formatting". However I'm interested in changing a cell color based on some other cell value.

For example, I want cell A4 to be painted red only if cell B4 has a greater value than A4 cell. Is there any way to accomplish that?

I've found this guide which actually explains what I need pretty well, but how can I expand this formatting to numerous cells? Just dragging doesn't work here for me.

EDIT: I'm using the Excel 2007 version.


Conditional formatting in Excel 2007 (based on value of another cell)

  1. Select the cell you want to format, “A1″ in this example.

  2. In the Home tab, Styles group, click on Conditional Formatting.

  3. Select New Rule.

    enter image description here

  4. The New Formatting Rule window will open.

    enter image description here

  5. Click Use a formula to determine which cells to format.

  6. The New Formatting Rule window will now look like this:

    enter image description here

  7. In the edit box labeled “Format values where this formula is true“, enter “=” followed by a formula referencing the other cell. In this example the formula: “=B1<0″ is entered.

  8. Click the Format button. The Format Cells window will open.

    enter image description here

  9. Specify the formatting you want. In this example I changed the cell color to “RED”.

  10. Click OK to close The Format Cells window.

  11. Click OK to close The New Formatting Rule window.

To summarize this example: cell A1 will have a RED color when cell B1 value is less than zero.

  • Thank you for your answer. However it seems that these directives are for older version of Excel. I use 2007 version and I can't perform these steps directly. I will update my question with Excel version.
    – Eugene S
    Jun 28 '12 at 16:58
  • 1
    @EugeneS changed to Excel 2007
    – Diogo
    Jun 28 '12 at 17:17
  • Thank you for your effort! Can you just please add an instruction as to how can I expand this conditional formatting to multiple cells? For example I want cells A1 to A5 be dependent on cells B1 to B5 respectively.
    – Eugene S
    Jun 28 '12 at 17:26
  • 2
    Just use the + drag icon and drop until the row that you want after applying the rule... I just this it here and it worked for me..
    – Diogo
    Jun 28 '12 at 17:47
  • 1
    The dragging option to expand wouldn't be feasible for the example given in the question. As A1 contains a value to be compared to B1, dragging A1 will also change the value of cells A2:A5 not just the formattting. You can choose a range, Eugene, (at step 1 in Diogo's instructions) - when you do that in conditional formatting the formula you use applies to the first cell (top left) in the range and the formula implicitly adjusts for each cell in the range in the same way that a formula would change if it were in a worksheet cell and you dragged it...... Jun 29 '12 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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