Feb 25 2021 06:46 PM
Feb 25 2021 09:15 PM
Since you want to automate the exercise so that your need is VBA macro.
Use this VBA code as Standard module.
Private Sub Worksheet_Change(ByVal Target As Range) Dim Choice As String Choice = Range("F1").Value If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub On Error Resume Next If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Application.EnableEvents = False If Choice = "Upper" Then Target = StrConv(Target, vbUpperCase) End If If Choice = "Lower" Then Target = StrConv(Target, vbLowerCase) End If If Choice = "Proper" Then Target = StrConv(Target, vbProperCase) End If Application.EnableEvents = True End If On Error GoTo 0 End Sub
How it works:
In VBA code Range("A1:A10")) is editable & you many set as your need.
Feb 25 2021 11:22 PM
If you're looking for a formula, you could try:
But, as Rajesh stated, if you want it to happen automatically, then you will need vba. Should it apply to the entire worksheet, or just particular cells? Another way I've seen it done is to do a bitwise xor:
Private Sub Worksheet_Change(ByVal Target As Range) Const ascDiff As Long = &H20 Dim rangeToChange As Range Dim cell As Range Set rangeToChange = Me.Range("A1:A10") If Intersect(Target, rangeToChange) Is Nothing Then Exit Sub End If Application.EnableEvents = False For Each cell In Intersect(Target, rangeToChange).Cells If (Not Target.HasFormula) And (Len(Target.Value) = 1) Then Target = Chr(Asc(Target.Value) Xor ascDiff) End If Next cell ExitProc: Application.EnableEvents = True Exit Sub ErrHandler: MsgBox "Error " & Err.Number & ": " & Err.Description Resume ExitProc End Sub