Case sensative list validation

[Guest]

Is there a way to validate against a list and return invalid if it does not
match the case of the list. Here is the code i'm using now in my macro:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$IF$5:$IF$20000"
.InCellDropdown = False
End With

One of the cells in the list contains "Hardwood Lumber - BF," however it
says that "hardwood lumber - bf" is valid. Any ideas?

 

[Bob Phillips]

You could try this

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=EXACT(" & ActiveCell.Address(False, False) &
_
",INDEX($I$5:$I$20000,MATCH(" & ActiveCell.Address(False, False)
& _
",$I$5:$I$20000,0)))"
.InCellDropdown = False
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob, but i got a compile syntax error when i tried to run it. I'm kind
of a novice with VBA so I don't know how to fix it.

 

[Bob Phillips]

maybe wrap-around. Try this version

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, _
AlertStyle:=xlValidAlertStop, _
Operator:= xlBetween, _
Formula1:="=EXACT(" & ActiveCell.Address(False, False) _
& ",INDEX($I$5:$I$20000,MATCH(" _
& ActiveCell.Address(False, False) _
& _",$I$5:$I$20000,0)))"
.InCellDropdown
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)