Matching Record Duplication Prevention with Validation Drop Down Lists

I am trying to get it working so that I can have a list with duplicate prevention but at the same time have it where a data validation drop down list can be used. Any help appreciated

I've created a simple example below which works perfectly for manual entry of what I'm trying to achieve:

Example:

Visible

[TABLE="width: 397"]

A

B

C

1

Ford

Mondeo

FordMondeo

2

Ford

Focus

FordFocus

3

Vauxhall

Astra

VauxhallAstra

4

Renault

Megane

RenaultMegane

5

Renault

Clio

RenaultClio

6

Ford

Focus

FordFocus

[/TABLE]

Ford is a none permitted entry in A6 due to the validation formula.

Same code showing data validation present in column A and cell formula in column C

[TABLE="width: 434"]

A Cells containing Data validation custom formula

Cell formula

A

B

C

1

=COUNTIF(C:C,C1)=1

Mondeo

=A1&B1

2

=COUNTIF(C:C,C2)=1

Focus

=A2&B2

3

=COUNTIF(C:C,C3)=1

Astra

=A3&B3

4

=COUNTIF(C:C,C4)=1

Megane

=A4&B4

5

=COUNTIF(C:C,C5)=1

Clio

=A5&B5

6

=COUNTIF(C:C,C6)=1

Focus

=A6&B6

[/TABLE]

What I wanted to do is have it so that the custom data validation formula used in column A is instead in VBA. Then 'Column A' should instead contain a drop down list using the Data Validation List allowing for selecting an option e.g. Ford, Vauxhall, Renault, . I already have an extensive set of drop down lists using indirect vlookups, so implementing the list selection drop down another way to do a list would not be ideal.

I found a previous post which does something similar to what I'm looking for (http://www.ozgrid.com/forum/showthread.php?t=157244) but I think it needs the variable 'r' to carry the value to the formula like what I have in column C but when a change occurs in column A and then preventing entry to the Cell in column A.

In my finished spreadsheet the solution also needs to be able to co-exist with another macro effecting the same cells which clears the values of other drop downs when primary ones are changed:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 6 Then




            Application.EnableEvents = 0




            .Cells.Offset(, 1).ClearContents
            .Cells.Offset(, 2).ClearContents
            .Cells.Offset(, 3).ClearContents
            .Cells.Offset(, 4).ClearContents
            .Cells.Offset(, 5).ClearContents
            .Cells.Offset(, 6).ClearContents
            .Cells.Offset(, 7).ClearContents




            Application.EnableEvents = 1




    End If: End With
    With Target
        If .Column = 7 Then




             Application.EnableEvents = 0




            .Cells.Offset(, 6).ClearContents




            Application.EnableEvents = 1




    End If: End With








End Sub

Re: Matching Record Duplication Prevention with Validation Drop Down Lists

Thanks done