In this post we shall apply below shown formatting to text in a cell –
- Change font colour
- Change font style as Italic
- Underline the text in cell
- Strike through the text
- Add super script to text
- Add sub script to text
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.
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
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
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
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
Post you may like