How to edit cell content without changing the format

[AmruthSagar]

Hi All,

I need a VBA code to capitalize first letter in the cell without changing the cell format or without changing the text colors (please refer sample file). As you can see in the sample file i have multiple text colors within the cell. These format should be maintained intact while capitalizing the first letter.

I tried doing this but the cell format is changing or it is successful if the entire text has single color. But that is not the case with my data.

So could anyone please help me with the macro. I would select the entire range of cell and run the macro.

Thanks a lot in advance.

Amruth

 

[jindon]

Try

Sub test()
    Dim r As Range, m As Object
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "((^|\.) *)([a-z])"
        For Each r In [a2].CurrentRegion
            If .test(r.Value) Then
                For Each m In .Execute(r.Value)
                    r.Characters(m.firstindex + Len(m.submatches(0)) + 1, 1).Text = UCase$(m.submatches(2))
                Next
            End If
        Next
    End With
End Sub

 

[Marc L]

Hi !

Select a cell and try this :​

Sub Demo()
    With ActiveCell.Characters(1, 1)
        .Text = UCase$(.Text)
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[AmruthSagar]

@jindon @Marc Thanks a ton. Both the macros work fine for me.

I am new to VBA macros and slowly learning things.

Thanks once again guys.

 

[jindon]

Amruth

See the difference in D5.
You have a space as the first character.

 

[AmruthSagar]

Yes @jindon. I have intentionally put that. I would add a small line in your macro to remove the first spaces. Then i am good to go.

Thanks You.