Formatting based on change in cell content

[Corleone]

I have a spreadsheet which runs a macro that dumps the required information from various worksheets into "sheet01"

it contains a list of employees, shift times & Reference numbers.
it is sorted by reference numbers.
im most instances there are duplicate reference numbers so the output is something like..

101212
101212
101212
232321
232321
989981
999999
999999

i would like of possible to be able to add to the end of the macro, a conditional formatting procedure which colours the sheet 01 row data either black or white wherever there is a change in the reference number column just to make it visually easier to the user

thanks in advance

 

[Andrew Poulsom]

If your data is in column A starting at a1 you can use the conditional formatting formula:

=MOD(COUNTIF(A$1:A1,A1),2)=0

 

[Corleone]

If your data is in column A starting at a1 you can use the conditional formatting formula:

=MOD(COUNTIF(A$1:A1,A1),2)=0

Thanks for the respose
its not quite doing what i need however (probably due to my poor explanation)
if i apply the formatting above it will highight the rows as follows as it changes based on whether the cells above is differennt of not.

103746 - highlight
111972 - highlight
114880 - highlight
114880 - does not highlight
114990 - highlight
115293 - highlight
115345 - does not highlight
115345 - highlight

what i need it to do however is

103746 - highlight
111972 - not highlight
114880 - highlight
114880 - highlight
114990 - not highlight
115293 - highlight
115345 - not highlight
115345 - not highlight

so that it visually flags up records with the same reference in them in the same colour

cheers

 

[Andrew Poulsom]

This is what I got with my formula:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 width=64 align=right x:num>103746</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 align=right x:num>111972</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 align=right x:num>114880</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: red; HEIGHT: 12.75pt; COLOR: windowtext; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: style; mso-pattern: auto none" class=xl22 height=17 align=right x:num>114880</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 align=right x:num>114990</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 align=right x:num>115293</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 align=right x:num>115345</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: red; HEIGHT: 12.75pt; COLOR: windowtext; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: style; mso-pattern: auto none" class=xl22 height=17 align=right x:num>115345</TD></TR></TBODY></TABLE>

In your example why is 115345 not highlighted but 114880 is?