excelvbastringfont-style

Making variable strings of text italics in Excel VBA


I have a range of cells with varying lengths of text.

These can look like:
"(first)"
"Here's another (longer string of text) for the example"
"And yet another much (much much longer string of text for reasons) that goes on and on"

I want to italicize text between ().

All solutions I found online look for .Character lengths, as in how many characters to start/stop.
Is there a way to italicize everything between () instead?

I tried this code from an SO question but changed the parts I thought would matter:

Sub ITALICS()
Dim X As Long, BoldOn As Boolean
BoldOn = False 'Default from start of cell is not to italic'
For X = 1 To Len(ActiveCell.Text)
    If UCase(Mid(ActiveCell.Text, X, 1)) = "(" Then
        ItalicOn = True
        ActiveCell.Characters(X, 1).Delete
    End If
    If UCase(Mid(ActiveCell.Text, X, 1)) = ")" Then
        BoldOn = False
        ActiveCell.Characters(X, 1).Delete
    End If
    ActiveCell.Characters(X, 1).Font.Bold = BoldOn
Next
End Sub

All it did was remove the () from my cells.


Solution

  • Your current code only checks for the parentheses and deletes them, and also uses Bold instead of Italic, and without properly tracking the range between ( and ).

    Is this what you are tying? I have commented the code. Let me know if you still have questions.

    Option Explicit
    
    Sub Sample()
        Dim ws As Worksheet
        Dim rng As Range
        
        '~~> Change this to the relevant sheet
        Set ws = Sheet1
        
        '~~> Change this to the actual range
        Set rng = ws.Range("A1:A2")
        
        Dim aCell As Range
        Dim txt As String
        Dim startPos As Long, endPos As Long
    
        '~~> Loop through each cell in the selected range
        For Each aCell In rng
            '~~> Ignore cell which has formula
            If aCell.HasFormula = False Then
                txt = aCell.Value
                startPos = InStr(txt, "(")
                endPos = InStr(txt, ")")
    
                '~~> Ensure both ( and ) exist and are in the correct order
                If startPos > 0 And endPos > startPos Then
                    '~~> Apply italics only to the text between ( and )
                    '~~> Also we add +1 to startPos to skip the opening bracket
                    '~~> Finally Subtract 2 to exclude both parentheses from formatting
                    aCell.Characters(Start:=startPos + 1, Length:=endPos - startPos - 1).Font.Italic = True
                End If
            End If
        Next aCell
    End Sub
    

    BEFORE:

    enter image description here

    AFTER:

    enter image description here