change cell to UPPER case using conditional formatting

Question

Hi

I have a column "H" that has a Y or N entered.  When users enter this text sometimes they use lower case by mistake as its an easy mistake to make.  Is there anyway I can use a Conditional Format to change any oddballs to UPPER case?

If not does anyone have any other suggestions?  I have to keep this sheet as simple as possible in case I am not around in the future to make any required changes.

Thanks again

M

Answer 1

I created a schedule worksheet for my staff.  It looked awful as some entered in lower case and some in upper.    I changed the font to Felix Titling.  It is all caps.  Whenever someone enters a value as lower case it will automatically change it to upper.  It was a simple solution and it has worked for the past two years.  Hope this helps you.

Answer 2

If you think you may be interested in VBA to achieve uppercase. . . . .

Place the event code in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Column > 1 Then Exit Sub

    On Error GoTo ErrHandler

    Application.EnableEvents = False

    Target.Formula = UCase(Target.Formula)

ErrHandler:

Application.EnableEvents = True

End Sub

Gord

Answer 3

Hello,

case cannot be adjusted with formatting. Upper case are different characters than lower case.

You can use Data Validation with the list option and enter Y and N as the two possible options. Then no other text can be entered into the cell. You can show a drop-down in the cell or not.

Only very few Excel functions are case sensitive, so case does not really matter most of the time.

Can you tell us why you need these cells in upper case only? What processing do you have in place that requires upper case? Maybe that could be tweaked to accept lower case as well.

Answer 4

Data validation does not mean that you have to use the mouse. In the dialogue from the screenshot I posted, untick the box for "In-cell dropdown". The dropdown will now no longer show. You can type Y or N and it will not accept lower case. Copy the empty cell with the data validation to other rows as desired.

Answer 5

Thanks Teylyn

I have thought about Data Validation but for one letter it doesn't seem worth it as I am more of a keyboard user rather than a mouse user.

It is only a visual thing - it doesn't impact anything within the file.

looks like I'm stuck with a mix and match of upper and lower case Ys and Ns :o(

Appreciate the quick response.

Rgds

M