Script help!!

  • Thread starter Thread starter Nude Rooster
  • Start date Start date
N

Nude Rooster

I have a spread sheet that is about 40 columns wide. In 4 of these columns
is a list of 8 digit account numbers, now the people who update this dont
type them in one at a time, they copy and paste from their own spreadsheets
into the main spreadsheet and in these 4 columns every month we end up with
about 5-10 duplicates that we dont catch and are caught by our auditing
dept. I am wondering if there is a simple script that when a duplicate is
pasted, it would highlight the first one in the column in green, and any
duplicates under it in red. I do not want it to automatically delete them, i
just want them to be easily detected for review. And once they have been
reviewed the duplicate can be easily deleted hopefully w/o a vb error
popping up. can some one help? and he needs to do it for 4 different
columns..


Help is appreciated..

Thank you


Brandon Larson
 
Nude said:
I have a spread sheet that is about 40 columns wide. In 4 of these columns
is a list of 8 digit account numbers, now the people who update this dont
type them in one at a time, they copy and paste from their own spreadsheets
into the main spreadsheet and in these 4 columns every month we end up with
about 5-10 duplicates that we dont catch and are caught by our auditing
dept. I am wondering if there is a simple script that when a duplicate is
pasted, it would highlight the first one in the column in green, and any
duplicates under it in red. I do not want it to automatically delete them, i
just want them to be easily detected for review. And once they have been
reviewed the duplicate can be easily deleted hopefully w/o a vb error
popping up. can some one help? and he needs to do it for 4 different
columns..

Help is appreciated..
Click to expand...

In trying to avoid using any VBA, I came up with this solution. It
fails to distinguish the first appearing Account Number from later
ones. It might also require the format to be copied onto the new cells
as they are copied into the worksheet.

Select one of the cells in the four columns - go to Format |
Conditional Formating. Change 'Cell Value Is' to 'Formula Is'.
Enter the following for the formula
=COUNTIF($A:$D,"="&D31) > 1
(change $A:$D to you columns and D31 to the cell you are currently
editing)
Click on the Format... button and on the Pattern tab select a nice
highlighting color. 'OK' your way out.
Copy that cells format (copy, then Edit | Paste Special | Formats
(option)) to all of the appropriate cells.

Let me know if this meets your needs.

Matthew
 
Back
Top