Excel vba: make part of a line in bold

Question

I have excel cells that contain these entries:

name/A/date name/B/date name/C/date 

The contents of the cell are displayed on several lines in one cell. I would like to make only the "name" bold for all entries. I recorded a macro, and I think the solution should be something like this:

 ActiveCell.FormulaR1C1 = "name/A/date" & Chr(10) & "name/B/date" & Chr(10) & "name/C/date" With ActiveCell.Characters(Start:=25, Length:=4).Font .FontStyle = "Bold" End With 

I do not know how to get the initial value and the length of each record. Anyone got an idea?

Answer 1

Now this:

 lngPos = InStr(ActiveCell.Value, "/") With ActiveCell.Characters(Start:=1, Length:=lngPos - 1).Font .FontStyle = "Bold" End With 

Answer 2

Inspired by various studies over the past few days:

 Dim totalVals, startPos(), endPos(), i, j, strLen As Long Dim currLine As String ' Split the cell value (a string) in lines of text splitVals = Split(ActiveCell.Value, Chr(10)) ' This is how many lines you have totalVals = UBound(splitVals) ' For each line, you'll have a character where you want the string to start being BOLD ReDim startPos(0 To totalVals) ' And one character where you'll want it to stop ReDim endPos(0 To totalVals) ' The value of the current line (before we loop on ActiveCell.Value) is empty currLine = "" For i = 0 To totalVals ' For each line... ' Length of the string currently treated by our code : 0 if no treatment yet... strLen = Len(currLine) ' Here we parse and rewrite the current ActiveCell.Value, line by line, in a string currLine = currLine & IIf(currLine = "", "", Chr(10)) & splitVals(i) ' At each step (= each line), we define the start position of the bold part ' Here, it is the 1st character of the new line, ie strLen + 1 startPos(i) = strLen + 1 ' At each step (= each line), we define the end position of the bold part ' Here, it is just before the 1st "/" in the current line (hence we start from strLen) endPos(i) = InStr(IIf(strLen = 0, 1, strLen), currLine, "/") Next i ' Then we use the calculated positions to get the characters in bold For j = 0 To UBound(startPos) ActiveCell.Characters(startPos(j), endPos(j) - startPos(j)).Font.FontStyle = "Bold" Next j 

This may be a little exaggerated, but I tested it, and it works like a charm. Hope this helps!