Here is a neat hack for adjusting the format (font color, font style, font type, etc.) of specific text within cells in Excel.

Standard Excel functionality only allows you to update the entire format of a cell, and not specific content. But with the help of a short and simple macro, you can deploy this function, anytime.

The code for this illustration demonstrates how to update two format attributes – “Bold”, and text color. However, it can be customised to update other attributes, including Italics, font type, and font styles.

To programme the code, the attributes required are the starting point of the specific text to be formatted, and the length (number of characters) of the text. For the illustration above, the specific format had to be applied to the first names in a list of first and last names. Assuming that the list is ordered properly with all first names appearing first, then the last name, this is information that can be derived using the =FIND() function to locate the first space (which will usually be the separator between the first and last name) in the text. The length of characters will automatically be the difference between the starting point and the position of the first space.

The code for this hack is below. Comments explaining the code start with ‘:

Sub FormatText()


Dim FullChar As String
Dim LenChar As Long


For n = 2 To 20 'change 21 to the number of the last row in your list of items to be formatted


FullChar = Cells(n, 2).Text '2 refers to the column the list is on (column B) - update as required
LenChar = Application.WorksheetFunction.Find(" ", FullChar) 'this uses the =find() function to identify the position of the space separating the name, which is the natural end point of the text and gives the length of the text in focus


With Cells(n, 2).Characters(Start:=1, Length:=LenChar).Font '2 refers to the column the list is on (column B) - update as required
.Bold = True
.Color = RGB(13, 130, 13) 'use the "More Colors" functionality in Excel to get the RGB code of the particular color you prefer
End With


Next n


End Sub