Excel conditional formatting based on cell and adjacent cell values

excel conditional-formatting
Richard Newman picture Richard Newman · Apr 23, 2013 · Viewed 82.2k times · Source

Is it possible to format a cell in Excel based on the cell above it's value. Basically I'm compiling a spreadsheet and I want the cell to be highlighted in light green if the value has gone down since the entry above and I want it to go red if it has increased.

I'm sure its fairly simple but I just cant get it to work.

Any help would be greatly appreciated.

Answer

chuff picture chuff · Apr 23, 2013

Yes, all you need to do is to use Conditional Formatting with a formula.

For example, if the two cells are A1 and A2, with A2 being the cell you want to change color, then you would select A2 and choose Conditional Formatting from the Home ribbon. Then select New Rule and "Use a formula to determine which cells to format."

Then, in the formula bar that comes up enter =A2>A1 and choose the red fill as the format you want to use.

This only works one cell at a time; can't select a range of cell and expect each cell format to based on it's own adjacent cell

Then create another rule for cell A2 using the formula =A2<A1 and choose light green as the format.

enter image description here

Related questions

Conditional Formatting (IF not empty)

How do I conditionally format a cell so if not blank it is grey? I tried to do 'not equal', but it didn't work. I am using Windows Office 2003 with Windows XP at work. I don't see the same feature …

Read More
Change the color of cells in one column when they don't match cells in another column

I want to check if the values in one column are the same as values in another column. Whenever the values are not the same, I would like to change the color of these cells. For example: Column I, Column …

Read More
Conditional Formatting using Excel VBA code

I have Range object called DistinationRange which contains reference to range B3:H63 I want to apply the following two conditional formatting rules using Excel VBA code dynamically. (Because the range would not be same all the time) If Cell …

Read More