Rich Text Formatting After Carriage Return

Hello coding experts,

Hopefully a relatively straightforward problem. I have a list of products and descriptions setup in an excel table. I reference the product elsewhere in the workbook, and pull the description beneath it using two carriage returns, as such:

=C4&""&INDEX(MY_TABLE_DESCRIPTION,MATCH(C4,MY_TABLE_PRODUCT,0))

Can someone point me in the right direction for creating a loop that will format the description (text found after the carriage returns) as size 8 font? Please note that this will (hopefully) be applied to other multiple sheets.

Hello mcmahobt,

This will require a VBA macro as this can not be done using formulas. Since you are using formulas, it will be necessary to see your workbook to prevent the VBA macro from altering the formulas.

Thanks for the info Leith. I was aware this would require VBA, so hopefully someone such as yourself who is more skilled than I can power through this. See attached for a sample workbook. The goal is to have the description that is being pulled in the formula within cells in column B by the INDEX/MATCH formula after the carriage returns to be formatted as text that is size 8.

Hello mcmahobt,

Thanks for posting the workbook. Which worksheet should I be looking at?

Sorry for the confusion, I wanted to mimic my data as closely as possible to make things easier in the long run when implementing a solution. The sheet that I am hoping to run the VBA code on is Sheet2.

Hello mcmahobt,

In your first post you said:

Can someone point me in the right direction for creating a loop that will format the description (text found after the carriage returns) as size 8 font?

I am still not sure where you are referring to on Sheet2?

On Sheet2, there are merged cells in column B. Merged cell B4:23, for example, contains the formula:

=C4&"

"&INDEX(Table1[Description],MATCH(C4,Table1[Product],0))

This pulls the product name from column C, as well as the corresponding description of that product from Sheet1. What I'm hoping to achieve is a macro that will loop through the merged cells in column B, and format the INDEX/MATCH portion of the formulas found in each merged cell in column B, and format them to be size 8 text.

Hello mcmahobt,

Thanks, now it makes sense. That piece about column "B" is what was missing.

No worries, things often get lost over the airwaves. Please let me know if you have any more questions if you get a chance to look at the problem.

Hello mcmahobt,

Houston, we have a problem. It appears that the font size of a merged cell whose output is from a formula can only be one size. While my macro runs without error, the font size of the cells remains unchanged.

Sub ResizeDescriptions()

    Dim Cell As Range
    Dim i As Long
    Dim n As Long
    Dim row As Long
    Dim Rng As Range
    Dim RngBeg As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet
    
        Set Wks = Worksheets("Sheet2")
        
        Set RngBeg = Wks.Range("B4")
        Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)
        Set Rng = Wks.Range(RngBeg, RngEnd)
        
        If RngEnd.row < RngBeg.row Then Set Rng = RngBeg
        
            For row = 1 To Rng.Rows.Count
                Set Cell = Rng.Cells(row, 1)
                If Cell.MergeCells = True Then
                      ' Code to change description font size to 8.
                        i = InStr(1, Cell, vbLf & vbLf) + 2
                        n = Len(Cell)
                        Cell.Characters(i, n - i + 1).Font.Size = 8
                    row = row + Cell.MergeArea.Rows.Count - 1
                End If
            Next row
            
End Sub

Just to clarify your formula for others, it can be written this way...

=C4&REPT(CHAR(10),2)&INDEX(Table1[Description],MATCH(C4,Table1[Product],0))

Do'h!

Well, back to the drawing board then. I suppose I can split the merged cell and manually adjust the font size, then drag down the formulas manually. Relatively pain free. I appreciate the help regardless, thanks for taking a look!