Auto change font or something?

[Ramballah]

Hello everyone

For my worksheet i have this one table where i need to fill in either W or L. but i dont want to either use Shift the whole time or press capslock. I want it so i just type w and it will go to W and same with l going to L. Is this possible and if yes how is this possible?

Thanks
Ramballah

 

[Fluff]

How about a worksheet change event?

 

[Yongle]

Is it an Excel Table (created with Insert \ Table)?
Are both L and W in the same column?
Is the cell usually empty beforehand?

 

[Ramballah]

Is it an Excel Table (created with Insert \ Table)?
Are both L and W in the same column?
Is the cell usually empty beforehand?

No i did the entire layout and stuff my self.
Same column and its empty beforehand yes.

So for example:

As you can see it needs to be in the W/L column which is column I. The rows 1/20 are rows 9/28.

 

[Yongle]

this is an example of what Fluff suggested

right-click on sheet tab \ view code \ paste code below into the code window \ {ALT}{F11} to return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Row > 1 Then Target = UCase(Target)
End Sub

Save workbook as macro-enabled

 

[Ramballah]

this is an example of what Fluff suggested

right-click on sheet tab \ view code \ paste code below into the code window \ {ALT}{F11} to return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Row > 1 Then Target = UCase(Target)
End Sub

Save workbook as macro-enabled

Thanks this works for column B. i need it in column B aswell which i didnt tell but i need it in column I aswell. so in both the columns i need it. And everytime i press 1 w or l it turns really slow my excel file. any tips? if i edit the 2 to 9 it will do column i. but it still wont change the lagg. also after doing it 12 times my excel crashes?

 

[Yongle]

Thanks this works for column B. i

need it in column B as well

which i didnt tell but i need it in column I aswell. so in both the columns i need it. And everytime i press 1 w or l it turns really slow my excel file. any tips?

Which column is the other column - is it column I ?
Which version of Excel are you using?
How many rows and columns in your worksheet?
Are there any other worksheets in the workbook?
Are there lots of formula in the workbook?
Are you changing the value in several cells at the same time (eg with DragDown)?

 

[Ramballah]

Which column is the other column - is it column I ?
Which version of Excel are you using?
How many rows and columns in your worksheet?
Are there any other worksheets in the workbook?
Are there lots of formula in the workbook?
Are you changing the value in several cells at the same time (eg with DragDown)?

I need it for Colums: B & I
Excel 2016, downloaded with the office set.
This worksheet goes from column A to M row 2 to 118. with like 70% if it being formulas and 30% me typing the W and L in: B19:B118 and I9:I108
There are 2 other worksheets. with formulas and macro's in them. they are worksheets that are dependant of this work sheet. aka this worksheet is my master sheet.
Lots of formulas i guess yeah. atleast over 300 cells with formulas.
There is only 1 cell that changes about 30 cells. but i barely change that cell. its more like a exchange ratio cell which i barely need to change.
I hope this cleared it up?

 

[FormR]

I need it for Colums: B & I

Hi, here is another option you can try..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, A As Range
Set R = Intersect(Target, Range("B:B,I:I"))
If Not R Is Nothing Then
    Application.EnableEvents = False
    For Each A In R.Areas
        A.Value = Evaluate("IF(LEN(" & A.Address & "),UPPER(" & A.Address & "),"""")")
    Next A
    Application.EnableEvents = True
End If
End Sub

 

[Ramballah]

Hi, here is another option you can try..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, A As Range
Set R = Intersect(Target, Range("B:B,I:I"))
If Not R Is Nothing Then
    Application.EnableEvents = False
    For Each A In R.Areas
        A.Value = Evaluate("IF(LEN(" & A.Address & "),UPPER(" & A.Address & "),"""")")
    Next A
    Application.EnableEvents = True
End If
End Sub

This works amazing! thank you! no more lagg :D