Change text colour , add superscript , subscript , strike through , Underline ,Italic style – VBA Excel

subscript , superscript , strike through vba excel

Objective

In this post we shall apply below shown formatting to text in a cell –

  1. Change font colour
  2. Change font style as Italic
  3. Underline the text in cell
  4. Strike through the text
  5. Add super script to text
  6. Add sub script to text

Approach

In this VBA excel automation we have added some sample text in cell A1, A4 and A8 as we can see in the image below. In the image below, on left side we can see how the data looked like before executing the VBA code, while on the right side we can see the formatting done by the VBA code.

In this post we shall discuss only Strike through, Superscript , Subscript properties.

Strike Though

In cell A1, we had sample text “This is sample text”, which can be seen in the image below. We have used strikethrough property of font class. We can use this property to highlight any text which is not applicable or correct anymore.

With Range("A1")
.Font.Strikethrough = True
End With

 

Adding Superscript

In the code below we have added superscript for string “2nd “, we wanted to format “2nd “as 2nd. We have used “superscript” property of “Font” class. So, we have changed the format of characters “nd”. Here “n” is the second character in string “2nd” so staring position is 2. While length of “nd” is 2, which is reflected in the formula.

Range("A4").Characters(Start:=2, Length:=2).Font.Superscript = True

 

Adding Subscript

In the code below we have taken a sample string “CH3COOH” and we wanted to format it as “CH3COOH”. So, we have used “subscript” property of “Font” class. Here we wanted to change the formatting of substring “3”.Starting position of the substring “3” is third and we wanted to change the formatting of substring “3” only , so we have used the length as 1 in the formula.

Range("A8").Characters(Start:=3, Length:=1).Font.Subscript = True

 

subscript , superscript , strike through vba excel

Code

Sub ChangeTextFormatting()

With Range("A1")

.Font.Color = vbRed
.Font.Bold = True
.Font.Italic = True
.Font.Underline = True
.Font.Strikethrough = True

End With

'Change the superscipt for 2 characters starting at position 2
Range("A4").Characters(Start:=2, Length:=2).Font.Superscript = True

'Change the subscript for 1 characters starting at position 2
Range("A8").Characters(Start:=3, Length:=1).Font.Subscript = True



End Sub

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.font.subscript
https://docs.microsoft.com/en-us/office/vba/api/excel.font.superscript
https://docs.microsoft.com/en-us/office/vba/api/excel.font.strikethrough

 

Post you may like

Delete row based on cell value in VBA excel