And just to add to Jim's response, working with Conditional formatting formulas
can be confusing (well, to me anyway).

It's one time I think it's easier (but not required) to select the range you
want to apply that CF rule to.

You can also use a work-around from John Walkenbach's site:
http://j-walk.com/ss/excel/odd/odd07.htm



Jim Rech wrote:

You really cannot use the code I posted with what you want to do. All my
code does is open the dialog and then lets the user do whatever he wants.
We have no control over what goes in the dialog. In your case you want to
get one input from the user (I think) but other do everything else.

The only way I know of to do what you want is to ask the user for a range
first and then run your code, sticking the range he selected in your code.

This is an example of asking for a range:

Sub GetRg()
Dim Rg As Range
On Error GoTo BadRg
Set Rg = Application.InputBox("Select a range", , , , , , , 8)
Rg.Value = "You selected this range"
Exit Sub
BadRg:
MsgBox "You cancelled"
End Sub

Obviously you'd have to adapt this to your situation by building the code

Formula1:="=countif(A:A,$A1)<=1"

from Rg.Address in some way.

--
Jim
"neopolitan" wrote
in message ...
|
| Jim, I have wanted to know how to do this for a long time, but I need a
| little more explanation on how to employ your code. Where in the code
| that I have below would I insert your code? Obviously, I don't want
| the column (A) hard-coded in the macro, thus the need for a pause.
|
|
| Code:
| --------------------
| Sub ColDataValidation()
| '
| ' ColDataValidation Macro
| ' Macro recorded 10/19/2005 by neopolitan'
|
| '
| With Selection.Validation
| .Delete
| .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
| xlBetween, Formula1:="=countif(A:A,$A1)<=1"
| .IgnoreBlank = True
| .InCellDropdown = True
| .InputTitle = ""
| .ErrorTitle = ""
| .InputMessage = ""
| .ErrorMessage = ""
| .ShowInput = True
| .ShowError = True
| End With
| End Sub
| --------------------
|
|
| --
| neopolitan
|
|
| ------------------------------------------------------------------------
| neopolitan's Profile:
http://www.excelforum.com/member.php...nfo&userid=611
| View this thread: http://www.excelforum.com/showthread...hreadid=477565
|

--

Dave Peterson