CODEWITHSUNDEEP
excelvba
user366121
user366121

Reputation: 3271

excel vba: make part of string bold

I have excel cells which contain entries like this:

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

Cell content is displayed on multiple lines in the same cell. I would like to make only "name" bold for all entries. I recorded a macro and I think the solution must 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

What I don't know is how to get the start value and the length of each entry. Anyone got an idea?

Upvotes: 10

Views: 56705

Answers (4)

RBerman
RBerman

Reputation: 391

So, I used Copilot and asked it exactly this: write a vba subroutine for Excel that takes a single cell with multiple lines of text in it, checks each line of text for a ":" character, and makes the text bold from start of that line, through the ":".

In under a second it responded with the code below, which has several problems:

  1. "For each" loop control variables must be variants. It used a string.
  2. It used the start of the cell text (Start:=1), which will only work for the first line of a multi line text.

Some good ideas, like using Split, but not correct.

Sub BoldTextBeforeColon()

    Dim cell As Range

    Dim line As String
    Dim colonPosition As Long
    
    ' Set the target cell (change this to the desired cell)
    Set cell = ActiveSheet.Range("A1")
    
    ' Split the cell contents into lines
    Dim lines() As String
    lines = Split(cell.Value, vbLf)
    
    ' Loop through each line
    For Each line In lines
        ' Find the position of the colon
        colonPosition = InStr(line, ":")
        If colonPosition > 0 Then
            ' Make the text bold from the start of the line to the colon
            With cell.Characters(Start:=1, Length:=colonPosition - 1).Font
                .Bold = True
            End With
        End If
    Next line
End Sub

Upvotes: 0

Michael Brogan
Michael Brogan

Reputation: 51

The answers above are perfectly fine. Since its related I wanted to include a similar routine I wrote to solve a formatting thing in my wife's macros.

in her situation we were consolidating string and wrote the concatenation into a single cell separated by a vbCrLf (Chr(10)) in her final output it would look something like this

Category number 1:
Category # 2:
Category 3:

The length of each category was different, and the # of categories may vary from 1 cell to the next. The pasted subroutine worked great

Sub BoldCategory() 
RowCount = ActiveSheet.UsedRange.Rows.Count
Set MyRange = ActiveSheet.Range(Cells(2, 1), Cells(RowCount, 1))
For Each Cell In MyRange
    i = 1
    LineBreak = 1
    Do While LineBreak <> 0
        EndBoldPoint = InStr(i, Cell.Value, ":") + 1
        BoldLength = EndBoldPoint - i
        Cell.Characters(Start:=i, Length:=BoldLength).Font.FontStyle = "Bold"
        LineBreak = InStr(i, Cell.Value, Chr(10))
        i = LineBreak + 1
    Loop
Next Cell
End Sub

So the ":" was the character I was keying in on to get the end point. the Chr(10) told me when 1 line ended and the next line began. When the last line was reached instr returned 0 therefore the while loop exits.

Upvotes: 0

Arthur
Arthur

Reputation: 174

Inspired by various research in the last 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, i.e. 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

It might be a bit overdone, butI have tested it and it works like a charm. Hope this helps!

Upvotes: 0

user366121
user366121

Reputation: 3271

Have it now:

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

Upvotes: 15

Related Questions