Bernie and Harlan:

Here is the job summary I am working from. It's more detailed, but I don't
believe fundamentally different that what we have been discussing. Again,
thanks for all your help.

Columns (call them 'B & C') simply represent finite lists of values which
could appear in any of the cells in their respective columns: B representing
a three-alpha abbreviation of a plant, and C representing a four-digit model
year. There can be any combination of plants and model years, but only one
value per cell. Column A represents how we need the concatenation of these
two values to appear: ((Plant=ARL) AND (Model Year=2000))

This would be an example of how the display would appear had the user really
selected ARL in B7 and 2000 in C7. (They could, of course, choose any of the
plant abbreviations and model years.)

Understanding that columns B & C will likely be populated via Copy & Paste
and there could be as many as 45,000 rows, the task at hand is to inform the
user if there are values anywhere in columns B and C which are not members of
the designated lists and allow the user to make appropriate corrections (or
simply delete the selection. Column A will then concatenate whatever is
decided upon after the error checking of B and C. As usual, blanks anywhere
should be considered deliberate and not a part of the actual error checking,
however if there is a value in either column B or C, then the checker should
alert the user that its partner cell is blank. At that point, the user would
either have to supply the missing value, or delete the one entered. Two
blanks in a same row of columns B & C are permissible as well as two valid
values, but one value and one blank and/or invalid values are not permitted.
The values in A do not have to be unique (obviously, with 45,000 entries,
this is not mathematically possible, anyway).

The piece in (call them columns F & G) is very similar, only (for now) we
simply have a single link to worry about. There is a formula in Column F,
were desiring a simple cell-to-cell reference (using CONCATENATE to avoid
the display of a zero). Again, the data in G will be the result of a large
Copy & Paste operation. The values in F must be unique, and the error
checker should cleanly call out the duplicates €“ one by one €“ and allow the
user to step into the checking process and fix the corresponding values in
column G until all the values in F are indeed unique. Again, blanks in G
(and the resulting blanks in F) would be permitted.

Hope this detail makes things clearer!

Greg

"Bernie Deitrick" wrote:

Greg,

It isn't a macro, per se, it is an event, so it won't show up in the macro
dialog from Tools / Macros....

You can test for its presence by copying and pasting two cells of one row
onto a row beneath. That will force the cell in col C to be equal to
another cell, and should bring up the dialog.

All my code worked in all my testing, though I never used 45,000 rows. If
you can't get it to work, I can send you a working example, which you could
use for testing your system.

HTH,
Bernie
MS Excel MVP




"Gregory Winters" wrote in
message ...
Well, I guess this had to be a lot harder than I made it sound, huh? :-(
The code works GREAT. Problem is: I can't use it! I create a macro,
paste
the code into it, save it in the VBA editor, attempt to open the macro,
and...no macro! The name of the macro has vanished, but when I open the
Editor again and double click on the module, there is the code, right
where I
had pasted it.

I have never run across an instance where I have recorded a macro, then it
has literally disappeared. Ideas?

P.S. Harlan, conditional formatting is basically out of the question
since
it would force the users to scroll through ~45000 rows and 300 columns of
entries just to located those colored cells. Thanks, however, and I've
saved
your notes for the future.

Greg

"Bernie Deitrick" wrote:

Greg,

This will completely undo the entry:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
Application.EnableEvents = False 'Optional undo
Application.Undo 'Optional undo
Application.EnableEvents = True 'Optional undo
Exit Sub 'Optional undo
End If
Next myCell
End Sub

This will just flag which entries were bad:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
End If
Next myCell
End Sub

and this will just remove the offending entries and notify, one by one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
Application.EnableEvents = False
Intersect(myCell.EntireRow, Target).ClearContents
Application.EnableEvents = True
End If
Next myCell
End Sub



HTH,
Bernie
MS Excel MVP