Hi, i have a list on a worksheet (sheet1) which looks like this for
example. the list is in alphabetical order so any indentical cells are
next to each other.

AA
AA
AB
AB
AC
AC
AD
AG
AL
AM
AM

i am trying to write a macro which will go through each cell and
compare it with the next cell. if they are the same the second cell's
value is changed to an "*". i tried a couple of ways but everytime i
just crash excel.

the list would look like this after the macro was run.

AA
*
AB
*
AC
*
AD
AG
AL
AM
*


thanks in advance.

nick

Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i,"A").Value = Cells(i-1,"A").Value Then
Cells(i,"A").Value = "*"
End If
Next i

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nickm687" wrote in message
ups.com...
Hi, i have a list on a worksheet (sheet1) which looks like this for
example. the list is in alphabetical order so any indentical cells are
next to each other.

AA
AA
AB
AB
AC
AC
AD
AG
AL
AM
AM

i am trying to write a macro which will go through each cell and
compare it with the next cell. if they are the same the second cell's
value is changed to an "*". i tried a couple of ways but everytime i
just crash excel.

the list would look like this after the macro was run.

AA
*
AB
*
AC
*
AD
AG
AL
AM
*


thanks in advance.

nick

ha works a treat. and you make it look simple.

thank you very much.


Bob Phillips wrote:
Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i,"A").Value = Cells(i-1,"A").Value Then
Cells(i,"A").Value = "*"
End If
Next i

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nickm687" wrote in message
ups.com...
Hi, i have a list on a worksheet (sheet1) which looks like this for
example. the list is in alphabetical order so any indentical cells are
next to each other.

AA
AA
AB
AB
AC
AC
AD
AG
AL
AM
AM

i am trying to write a macro which will go through each cell and
compare it with the next cell. if they are the same the second cell's
value is changed to an "*". i tried a couple of ways but everytime i
just crash excel.

the list would look like this after the macro was run.

AA
*
AB
*
AC
*
AD
AG
AL
AM
*


thanks in advance.

nick