Two font sizes in one cell.

[AL 22]

Hi,
I would like to have some VBA code, please.

In this workbook, in sheet "Feuil3", in column D12:D29, I want the first two characters to be in font size 14 and the part between parentheses to be in font size 9.
I have a code which work (see module4), but which erase the formula, and I don't want that.

Please find a workbook with an example of what I want.

Best regards.

AL 22

 

[Monty]

Mr. AL

This code extracts the necessary parts from the formula, creates a new formatted text, and then applies the formatting without erasing the original formula.

Adust as per your requirement

Sub FormatTextWithoutErasingFormula()
    Dim ws As Worksheet
    Dim cell As Range
    Dim formulaText As String
    Dim newText As String
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Feuil3")
    
    ' Loop through the specified range
    For Each cell In ws.Range("D12:D29")
        ' Get the current formula
        formulaText = cell.Formula
        
        ' Extract the first two characters
        Dim firstTwoChars As String
        firstTwoChars = Left(formulaText, 2)
        
        ' Extract the text between parentheses
        Dim startParenthesis As Long
        Dim endParenthesis As Long
        startParenthesis = InStr(formulaText, "(")
        endParenthesis = InStr(formulaText, ")")
        Dim betweenParentheses As String
        betweenParentheses = Mid(formulaText, startParenthesis + 1, endParenthesis - startParenthesis - 1)
        
        ' Create the new text with formatting
        newText = firstTwoChars & betweenParentheses
        
        ' Update the cell with the new formatted text without erasing the formula
        cell.Value = newText
        cell.Characters(1, 2).Font.Size = 14 ' Set font size for first two characters
        cell.Characters(startParenthesis, endParenthesis - startParenthesis).Font.Size = 9 ' Set font size for text between parentheses
    Next cell
End Sub

 

[AL 22]

Monty,

Thank you very much for your quick response,

but I have an error 1004 - Application-defined or object-defined error - on the line : cell.Value = newText.

Do you have a workaround ?

Thanks again.

AL 22

 

[Fluff13]

I have a code which work (see module4), but which erase the formula, and I don't want that.

If the cell contains a formula, then you cannot have multiple formats in that cell. You can either have the formula, or the format, but not both.

 

[AL 22]

Fluff13,

Thank you for your response.

You confirm what I thought because I made quite a few attempts, but each time the result is the same, that is to say the impossibility of having two formats in a cell...
So I'm going to do it differently.

Thanks again and have a good day.
AL 22

 

[Fluff13]

Glad to help & thanks for the feedback.