How can this Micro customized. Please help

[Help on formula]

Sub Names()

For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B"))
If Len(cll.Value) < 4 And cll.Value <> "" Then cll.Value = Left(cll.Value &
",,,,", 4)
cll.Value = UCase(cll.Value)
Next cll

End Sub

This is an old micro which works perfectly. I need help to make two changes
into it.

1. Column A and B are text column for names, instead of capital letters I
want those column in sentence case i.e. ANDREW into Andrew. (Everything else
should stay the same, comas to make it four if characters are less than four)

2. Column C which is also a text column for names, I wan it to be
capitalized once I press the “Name Correction†micro.

Any help to customize the above micro for this purpose will be greatly
appreciated. I thank you for considerations in regard.

 

[Roger Govier]

Hi

Try

Option Explicit

Sub Names()
Dim cll As Range
For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B"))
If Len(cll.Value) < 4 And cll.Value <> "" _
Then cll.Value = Left(cll.Value & ",,,,", 4)
cll.Value = UCase(Left(cll.Value, 1)) & Mid(cll.Value, 2, 255)
Next cll

End Sub

Sub NameCorrection()
Dim cll As Range
For Each cll In Intersect(ActiveSheet.UsedRange, Columns("C:C"))
cll.Value = UCase(cll.Value)
Next cll

End Sub

 

[Help on formula]

Thank you very much Roger. I deleted

End Sub

Sub NameCorrection()
Dim cll As Range

and it work great (since I removed these lines, I don’t know if technically
something else need to add or remove from the code. Name Correction is just a
name of micro, I right click and edit changes into it. The only problem I
have now is the list of names which already stored in upper case (in column
A,B and C) and formula not changing it unless I re-enter in lower case. I
realize I should mention this in my question. Thanks for helping

 

[Roger Govier]

Hi

Then change the line
cll.Value = UCase(Left(cll.Value, 1)) & Mid(cll.Value, 2, 255)

to
cll.Value = UCase(Left(cll.Value, 1)) & Lcase(Mid(cll.Value, 2, 255))

 

[Help on formula]

Thank you very much. It works perfect now. I salute your knowledge and
courtesy.
Thanks again