Format Characters in the Cell using Excel VBA
Today’s article we are going to write Format Characters in the Cell using Excel VBA, so today we will understand how to format a specified character with the help of VBA, it is possible to format different parts of a text string with the help of some VBA code. Now as we have been given a string, how will we format the specified text in it? It is possible to format different parts of a text string with the help of some VBA code.
In excel worksheet we have options for formatting manually, but how we do it in VBA is what we will learn in this article. As we know that it is very easy to do manual formatting in excel, similarly it is easy to do in VBE too, let’s understand.
How to Format Characters in the Cell
DOWNLOAD USED EXCEL FILE FROM HERE>>
Through this post, we will know how to be formatting in text string in worksheet with the help of VBA, then we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Once we insert the module, we will write a subroutine as format text .
Sub formattext() End Sub
And then Define variable for range, characters, long and variable for string.
Sub FormatText() Dim WS As Worksheet Dim Cell As Range Dim Chs as Characters Dim stNo As Long Dim chLen As Long Dim txt As String Dim fTxt As String End Sub
Now we will set the worksheet
Sub FormatText() Dim WS As Worksheet Dim Cell As Range Dim Chs As Characters Dim stNo As Long Dim chLen As Long Dim txt As String Dim fTxt As String Set WS = ActiveSheet End Sub
Set the range of cells contain the data. If you go to the worksheet and see(B3), you will get this data.
Sub FormatText()
Dim WS As Worksheet
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
End Sub
Now we will store the cell value in ftxt variable.
Sub FormatText()
Dim WS As Worksheet
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
End Sub
Now we will store the text, with double inverted column. which we will be formatting.
Sub FormatText()
Dim WS As Worksheet
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
End Sub
Now we will use inStr function, which is used to return the position of the first occurrence of a substring in a string.
Sub FormatText()
Dim WS As Worksheet
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
stNo = InStr(fTxt, txt)
End Sub
Now we will use ” IF ” statement, when we will use the cell characters function for set character.
Sub FormatText()
Dim WS As Worksheet
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
stNo = InStr(fTxt, txt)
If stNo > 0 Then
Set Chs = Cell.Characters(stNo, Len(txt))
End If
End sub
Do formatting so we have to do with character (With Chs), and then we will write for the .with font
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
stNo = InStr(fTxt, txt)
If stNo > 0 Then
Set Chs = Cell.Characters(stNo, Len(txt))
With Chs
With .Font
End With
End With
End If
End Sub
then we will use color function, in color function we will give integer to color. after this we will add bold to the characters font and make bold true.
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
stNo = InStr(fTxt, txt)
If stNo > 0 Then
Set Chs = Cell.Characters(stNo, Len(txt))
With Chs
With .Font
.Color = RGB (171, 171, 171)
.Bold = True
End With
End With
End If
End Sub
we will make the font of the character true by adding italics, so we will be formatting the txt italics, we will also be formatting underline to the font
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
stNo = InStr(fTxt, txt)
If stNo > 0 Then
Set Chs = Cell.Characters(stNo, Len(txt))
With Chs
With .Font
.Color = RGB (100, 50, 10)
.Bold = True
.Italic = True
.Underline = True
End With
End With
End If
End Sub
after all, now we will click run button
After the run button, we will go to the worksheet to see whether the code worked and as you can see it has been applied here.
We can use color index instead of RGB for color
Sub FormatText()
Dim WS As Worksheet
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
stNo = InStr(fTxt, txt)
If stNo > 0 Then
Set Chs = Cell.Characters(stNo, Len(txt))
With Chs
With .Font
.Colorindex = 25
.Bold = True
.Italic = True
.Underline = True
End With
End With
End If
End Sub
Now we will make the code, then we will run the code, then we will go to the worksheet, then the color has changed.
What will be the result if we make formatting italic and underline false?
Sub FormatText()
Dim WS As Worksheet
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
stNo = InStr(fTxt, txt)
If stNo > 0 Then
Set Chs = Cell.Characters(stNo, Len(txt))
With Chs
With .Font
.ColorIndex = 25
.Bold = True
.Italic = False
.Underline = False
End With
End With
End If
End Sub
Run the code and the result in the worksheet has changed.
Now we will add superscript to the text let’s go to VBE
Sub FormatText()
Dim WS As Worksheet
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
stNo = InStr(fTxt, txt)
If stNo > 0 Then
Set Chs = Cell.Characters(stNo, Len(txt))
With Chs
With .Font
.ColorIndex = 25
.Bold = True
.Italic = False
.Underline = False
.Superscript = True
End With
End With
End If
End Sub
Now we’ll run the code and then we’ll go to the worksheet and see that the text is in superscript.
Make the text superscript false and then add the strikethrough formatting. Let’s go to VBE once again.
Sub FormatText()
Dim WS As Worksheet
Dim Cell As Range
Dim Chs As Characters
Dim stNo As Long
Dim chLen As Long
Dim txt As String
Dim fTxt As String
Set WS = ActiveSheet
Set Cell = WS.Range("B3")
fTxt = Cell.Value
txt = "personified animals"
stNo = InStr(fTxt, txt)
If stNo > 0 Then
Set Chs = Cell.Characters(stNo, Len(txt))
With Chs
With .Font
.ColorIndex = 25
.Bold = True
.Italic = False
.Underline = False
.Superscript = False
.Strikethrough = True
End With
End With
End If
End Sub
Then add strikethrough and run the code, then go to the worksheet, strikethrough formatting has been added to the text.
You can also remove it by going to VBE and setting it too false.
So, I hope you have understood Format Characters in the Cell using Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about Format Characters in the Cell using Excel VBA
