Macro - highlight rows

[Guest]

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

 

[Bob Phillips]

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

Select all the rows.
Menu Format>Conditional 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)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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