Change row colour when value changes

[RubberChicken]

Hi guys

I have a spreadsheet with thousands of rows.
I want to break the row colour up by the reference in column A.
2 alternating colours is fine. It is only to make the data easier to read and see the groups.
So the reference is repeated in column A over a variable amount of rows. Then a new reference for the next few rows and so on.
I'd like the row colour to change when the reference changes.
I think I have seen it done with a =MOD formula and conditional formatting, but I can't find that now.
Any ideas?

Cheers

 

[Legacy 396030]

Sheet2

	A	B	C
1	ID	Name	XXX
2	2163	Heather	45

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 85.6px;"><col style="width: 85.6px;"><col style="width: 85.6px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 {=IFERROR(INDEX(Sheet1!B$2:B$20,MATCH($A2,Sheet1!$A$2:$A$20,0)),"")} C2 {=IFERROR(INDEX(Sheet1!C$2:C$20,MATCH($A2,Sheet1!$A$2:$A$20,0)),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[FormR]

This is getting a bit off topic - I'm using a newer version so maybe that is why. Note that I can't find an English version of the version I'm using though.

For that reason I usually use the MrExcel HTML maker, and only resort to Excel Jeanie when I want to show conditional formatting.

 

[RubberChicken]

Yes! Thank you FormR
That's given me exactly what I need.
I don't know what "NOT(N(B1)"is doing but it works.

Thanks BillyRay, Thanks FormR

 

[Legacy 396030]

Glad to help and thank you too for being part of the forum. I appreciate this resource very much.

Also thank you FormR and sorry to ask something off topic.

 

[FormR]

I don't know what "NOT(N(B1)"is doing but it works

Glad we could help N() is being used to avoid an error on the first row. NOT() is just flip flopping the TRUE/FALSE as the values in column A change.

Also thank you FormR and sorry to ask something off topic.

Not a problem by me