Change formatting at each change of ID

[Twilkes]

I like some of the table autoformatting from Excel 2007, especially the way it alternates colours on different rows, so people can follow along the rows easily.

But is there an easy way to format a table so that the colour is not changed on each row, but only when the contents of a certain field change, like an ID? Example below.

Would I need to set up a macro for this or is there something built into Excel that would do this?

[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]ID[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Data[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dbe5f1"]1[/TD]
[TD="class: xl66, bgcolor: #dbe5f1"]A[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dbe5f1"]1[/TD]
[TD="class: xl66, bgcolor: #dbe5f1"]B[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dbe5f1"]1[/TD]
[TD="class: xl66, bgcolor: #dbe5f1"]C[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #c5d9f1"]2[/TD]
[TD="class: xl67, bgcolor: #c5d9f1"]D[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #c5d9f1"]2[/TD]
[TD="class: xl67, bgcolor: #c5d9f1"]E[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dbe5f1"]3[/TD]
[TD="class: xl66, bgcolor: #dbe5f1"]F[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #c5d9f1"]4[/TD]
[TD="class: xl67, bgcolor: #c5d9f1"]G[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dbe5f1"]5[/TD]
[TD="class: xl66, bgcolor: #dbe5f1"]H[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dbe5f1"]5[/TD]
[TD="class: xl66, bgcolor: #dbe5f1"]I[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Skybluekid]

You can use Condtional Formating>>

see Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples) - YouTube

 

[Twilkes]

You can use Condtional Formating>>

see Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples) - YouTube

Thanks that's a good tip, I'd not considered that.

I've got it working by adding another column that checks whether the ID above the previous one is different, and if it is, it adds 1 to a running count. So the running count for the first ID is 1, the next goes up to 2, then 3 etc. I then do a conditional format on the running count, using MOD(cell,2) to determine whether it's odd or even, and changing the colour of all the odd ones.

So it works, but is there a way to do it without adding in the running total column? The IDs are alphanumeric, like KRG154, KEH872, GTD292, so it's difficult to do anything meaningful on them.

Thanks,