Can I force a cell to Proper Case if is already using Conditional Formating and Data Validation?

[USAMax]

My client wants, "Yes" or "No" in proper case so if someone enters one of them in upper or lower case the Data Validation gives them an error. Can this be changed to proper case before Data Validation kicks in?

I would rather not do this in a macro if there is another way.

Thank you,

 

[Rick Rothstein]

Thanks Rick, you have always come through for me. Unfortunately this client has a lot of Data Validation and they want it to work just like all the others, with a drop-down but they also want their client to be able to type the answer or have Excel "Type Ahead". I already have the Worksheet_Change setup and I could just plug this in but it is not what they want.

What I proposed should not interfere with the drop down lists... the code will proper case an entry of "yes" or "no" (any casing of the letters in any combination, eg, yEs, nO, etc) no matter how it gets into the cell (as long as that something is not a formula).

You are amazing Rick you must help a lot of people considering how much you have helped me. Thank you!

You are quite welcome for all of my past postings which ended up proving useful to you.

 

[Andrew Poulsom]

Thank you shg,

I entered the following code into the Data Validation:

OR(Exact(SNCODE.YesNo, "Yes"), Exact(SNCODE.YesNo, "No"))

In the drop-down window I get two options
OR(Exact(SNCODE.YesNo, "Yes")
Exact(SNCODE.YesNo, "No"))

If I add the equals sign before it I get the error:
"The list source must be a delimited list, or a reference to single row or column."

You need to use Custom not List.