change cell shading whenever contents different from previous cell

[Guest]

Hi! I am generally familiar with Excel, but need some help on this
particular problem.

I want to apply a format where: whenever the number in a column differs
from the one previous to it, a cell shading color change is applied.

For example, you have a column of numbers in Excel:

2
2
2
5 (cell shading color change applied)
5
5
5
2 (cell shading color change applied)
2
2
3 (cell shading color change applied)
etc....

It doesn't matter what color is used, just so that it is applied whenever
there is a change from the previous number.

There has got to be an easy answer to this.... thanks so much in advance for
any help!

 

[Earl Kiosterud]

Zooey,

Easy answer? Pretty much, and you'll get a mess of them -- I don't know why
I'm throwing in, but here goes.

You'll have to select the column (or portion thereof), and the active
(white) cell must not be in row 1 (well, it could be, but it's confusing).
Press Enter to move it down after you've made your selection. I'll presume
A2 is active.

Format - Conditional format - Formula is: = A2<>A1
Set your format, and OK your way out of the whole thing.

The formula will be copied into the conditional formatting of the other
(selected) cells. A1, if in the selection, will have something like
=A1<>A65536, indicating that it wraps.

If you want the row to be highlighted (not just the cell in the column),
it's all the same, except you select the rows, not just the column of
interest.

 

[ynissel]

You can try conditional formatting under format conditional formannting -
and say if this cell is not equal to the previous cell.
Good luck.

 

[bigwheel]

Another way uses a bit of VBA. Not to neat but it does the trick. I expect
someone will come up with a more concise solution;

For a = 2 To 20 'or whatever the range is
If Cells(a, 1) < Cells(a, 1).Offset(-1, 0) Or Cells(a, 1) > Cells(a,
1).Offset(-1, 0) Then
Cells(a, 1).Interior.ColorIndex = 6
End If
Next