Apply ProperCase to dynamic range in a column

[Vincent88]

Hi, Appreciate any help to modify the code to apply the function to the dynamic range in a column only not whole column ?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastRow As Long
LastRow = Range("L2").End(xlDown).Row

If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo ErrHandler:

If Target.Column = 12 Then
If Not IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastRow As Long
LastRow = Range("L2").End(xlDown).Row

If Target.CountLarge > 1 Then Exit Sub
On Error GoTo ErrHandler:

If Not Intersect(Target, Range("L2:L" & LastRow)) Is Nothing Then
If Not IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Vincent88]

HI Fluff, Thanks. Your code works. Meantime I have another Uppercase function in any other range. The code works also but I wonder if my code can be modified more neatly. (Like

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Sh.Name
    Case "Data"
      Exit Sub
    Case Else
  End Select

'ProperCase in Column A

Dim LastRow As Long
LastRow = Range("A3").End(xlDown).Row


If Target.CountLarge > 1 Then Exit Sub
On Error GoTo ErrHandler:

If Not Intersect(Target, Range("A3:A" & LastRow)) Is Nothing Then
If Not IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
'ErrHandler:
'Application.EnableEvents = True

'UpperCase From Range(C:AG)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo ErrHandler:
If Not Intersect(Target, Range("C3:AG" & LastRow)) Is Nothing Then
If Not IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Value = UCase(Target.Text)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True



End Sub

If Target.CountLarge > 1 Then Exit Sub. I have to write this twice)

 

[Fluff]

There is no need to use this twice

If Target.CountLarge > 1 Then Exit Sub

also you can get rid of the 2nd occurrence of

On Error GoTo ErrHandler:

 

[Vincent88]

Hi Fluff, Thanks a lot

 

[Fluff]

My pleasure.

 

[Vincent88]

Hi Fluff, Is it possible to amend the code run as array in order to run faster ! Current situation is that there is time lagging whenever I make input. I saw a youtube which demonstrated this method. Thanks on advance.

 

[Fluff]

There is nothing to put into an array, it just changes a single cell.