Hi everybody,

I have a chunk of records stored in excel and are tracked by account number.
Some of the account numbers are duplicated and I wish to run a macro which
can loop through the records and highlight the row s which have duplicated
account numbers. How do i do it? Thanks in advance

You don't need a macro, conditional formatting will do it.

Select all the rows.
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =COUNTIF($A:$A,A1)1
Click Format, select the Pattern tab, and choose a colour
OK out

This assumes the account is in column A, and you start at row 1.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Zen" wrote in message
...
Hi everybody,

I have a chunk of records stored in excel and are tracked by account
number.
Some of the account numbers are duplicated and I wish to run a macro which
can loop through the records and highlight the row s which have duplicated
account numbers. How do i do it? Thanks in advance

It does not work. It highlighted wrong records which are not duplicated

"Bob Phillips" wrote:

You don't need a macro, conditional formatting will do it.

Select all the rows.
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =COUNTIF($A:$A,A1)1
Click Format, select the Pattern tab, and choose a colour
OK out

This assumes the account is in column A, and you start at row 1.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Zen" wrote in message
...
Hi everybody,

I have a chunk of records stored in excel and are tracked by account
number.
Some of the account numbers are duplicated and I wish to run a macro which
can loop through the records and highlight the row s which have duplicated
account numbers. How do i do it? Thanks in advance

Works for me, although it is better if the formula is

=COUNTIF($A:$A,$A1)1


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Zen" wrote in message
...
It does not work. It highlighted wrong records which are not duplicated

"Bob Phillips" wrote:

You don't need a macro, conditional formatting will do it.

Select all the rows.
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =COUNTIF($A:$A,A1)1
Click Format, select the Pattern tab, and choose a colour
OK out

This assumes the account is in column A, and you start at row 1.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Zen" wrote in message
...
Hi everybody,

I have a chunk of records stored in excel and are tracked by account
number.
Some of the account numbers are duplicated and I wish to run a macro
which
can loop through the records and highlight the row s which have
duplicated
account numbers. How do i do it? Thanks in advance

Hi Bob, it works! It works!. Thanks for the assistance. :)

"Bob Phillips" wrote:

Works for me, although it is better if the formula is

=COUNTIF($A:$A,$A1)1


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Zen" wrote in message
...
It does not work. It highlighted wrong records which are not duplicated

"Bob Phillips" wrote:

You don't need a macro, conditional formatting will do it.

Select all the rows.
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =COUNTIF($A:$A,A1)1
Click Format, select the Pattern tab, and choose a colour
OK out

This assumes the account is in column A, and you start at row 1.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Zen" wrote in message
...
Hi everybody,

I have a chunk of records stored in excel and are tracked by account
number.
Some of the account numbers are duplicated and I wish to run a macro
which
can loop through the records and highlight the row s which have
duplicated
account numbers. How do i do it? Thanks in advance