Macro to convert predefined text only to lower case

**ghoneim** · 05-13-2013, 05:20 AM

Hi valued members,

I need a macro that enables me to select a range of cells by mouse, check their content for predefined text (mostly prepositions like "to, in, with, at, of..etc) and convert that certain text only into lower case, while leaving other textual content as is.

I appreciate your help.
Thanks in advance.

**JBeaucaire** · 05-13-2013, 05:51 AM

Like so... I could make it shorter, but this is the easiest to understand. Notice most of these short words include spaces before and after?

Option Compare Text

Sub ReplaceSpecial()
Dim cell As Range, MyStr As String

Application.ScreenUpdating = False
For Each cell In Selection
    MyStr = cell.Value
    MyStr = Replace(MyStr, " to ", " to ")
    MyStr = Replace(MyStr, "the ", " the ")
    MyStr = Replace(MyStr, " in ", " in ")
    MyStr = Replace(MyStr, " with ", " with ")
    MyStr = Replace(MyStr, " at ", " at ")
    MyStr = Replace(MyStr, " of ", " of ")
    MyStr = Replace(MyStr, " and ", " and ")
    MyStr = Replace(MyStr, " for ", " for ")
    cell.Value = MyStr
Next cell
Application.ScreenUpdating = True
End Sub