Combine text from different cells but apply different formatting

[attikuz]

Hello!

I have text in 3 different columns. Column B contains the name, Column C contains a summary and Column D contains a brief description.

I want to join Column B & Column D and Column A. I want column B to be a different font size and colour to the Columns D & A and I would like a paragraph separating all 3.

Here is an example of the data in these columns (note that each cell is actually using a vlookup to a different spreadsheet):

Column B (Name)	Column C (In Brief)	Column D (Activity)
Coats	"SOCIAL PERFORMANCE Health and safety and workplace diversity are identified as priority areas for the group’s employee strategy. The group tracks safety incidents and near misses in order to identify patterns of risk and update training as required. During 2017, Coats reported 0.55 safety incidents per 100 employees. This is a slight decrease on the previous year (0.56) and also compares favourably to the US average for textile mills of 3.1 incidents per 100 employees. Representation of women in Coats’ workforce has remained stable at 40% over recent years, though the proportion of women in senior roles has slowly increase from 18.6% in 2014 to 21.5% in 2017. Women also comprise 30% of the group’s board. Coats has policies in places covering human rights risks in its supply chain and corruption and bribery risks associated with its operations. These reference international norms and standards and have a level of detail appropriate to the nature of the group’s operations. Coats conducts risk assessments on its supply chain to identify priority areas for auditing and oversight. Audits, education and supplier engagement activities are then focused on the suppliers identified as being the highest risk. Coats conducts audits in-house, though it does not report on the number of supplier audits conducted nor the rate of non-compliance. Though not the focus of Coats’ activities, a number of the group’s product lines deliver social benefits. For example, it manufactures a range of medical-grade suture threads, flame and cut resistant yarns, and reflective tapes and trims for high-vis clothing. ENVIRONMENTAL PERFORMANCE A formal environmental policy covers all group operations. This primarily addresses direct impacts, but Coats also recognises the need to consider the impact of raw materials and the lifecycle of its products. The group also has policies and systems in place to manage, reduce and eliminate the discharge of hazardous chemicals as part of the dyeing process. Coats has improved its environmental reporting in recent years and a good level of data is now provided, alongside trends over time. The group’s greenhouse gas emissions have remained at broadly the same level since 2014 in absolute terms. However, emissions per $m revenue fell by 8.8% in the five years to 2017 as a result of efforts to make production more efficient. Coats’ energy use per kilogram of dyed product has also fallen over recent years, with a 3% decrease during 2017 and a 13% decrease since 2014. Water remains a significant impact for the group’s operations, particularly during the dyeing process. During 2017, Coats used 112 litres of water for every kilogram of dyed product, down 5% year-on-year and 12% since 2014. At the same time, Coats has invested in improved water treatment facilities to improve the quality of discharged wastewater and to increase the amount of water that can be recycled and reused. Over 90% of Coats’ products are made of synthetic, oil-based polymers. In addition to reducing the amount of waste generated in-house as a result of the production process, Coats is also exploring alternative sources of raw materials. For example, in 2017 the group launched its EcoVerde range which is made of recycled plastic bottle flakes that have been spun into thread."	Coats manufactures industrial yarns and threads. Its apparel and footwear divisions accounts for approximately two thirds of group revenue and provides industrial sewing threads, zippers and trims. The performance materials division produces specialist threads such as medical sutures, flame retardant yarn, and threads mixed with carbon and fibreglass. Finally, the crafts division produces yarns, threads and needlecraft items for the home market. The group is headquartered in the UK and operates manufacturing sites in over 50 countries.

I am stuck though as when i run my code i just get B4 D4 and C4 instead of the text that is in those cells...

Sub InBrief()

Dim Cash As Range
Dim Title, Activity, Description
Dim cell As Range
Dim CurrentRow, LastRow As Integer

Application.ScreenUpdating = False

CurrentRow = 2
 
'Clear sheet and filters
Sheets("In Briefs").Activate
Sheets("In Briefs").Range("$A$1:$H$1000").AutoFilter Field:=3
Sheets("In Briefs").Range("A3:H1000").ClearContents

'Copy SEDOLs from portfolio tab
Sheets("Portfolio").Range("A8:A100").Copy
Sheets("In Briefs").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues

'Remove all the blank rows
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A3:A" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'Copy the vlookup formulas to import the paras
Range("B2:D2").Copy
Range("B3:D" & LastRow).PasteSpecial xlPasteFormulas

'Join the header, activity and inbrief together and apply formatting
For Each cell In Range("F2:F" & LastRow)

If Range("C" & CurrentRow) <> "" Then
    With Application
        Title = "B" & CurrentRow
        Description = "C" & CurrentRow
        Activity = "D" & CurrentRow
        
    End With
        cell = Title & " " & Activity & " " & Description
        
    With cell
        .Characters(1, Len(Title)).Font.Color = vbCyan ' read 1st colour
        .Characters(Len(Title) + 1, Len(Activity)).Font.Color = vbRed ' read 2nd colour
        .Characters(Len(Title) + Len(Activity) + 1, Len(Description)).Font.Color = vbBlue ' read 3rd colour
     End With
End If

CurrentRow = CurrentRow + 1
Next

'Filter out any securities that have no data
Range("$A$1:$H$" & LastRow).AutoFilter Field:=3, Criteria1:="<>", Criteria2:="<>" & 0

Application.ScreenUpdating = True

End Sub

Ideally I want the font to be Georgia but for the name i want it to be green, bold and size 18 and for the other two size 12 and black.

Help appreciated or let me know if this is not possible!

 

[attikuz]

I have managed to solve the issue but I am limited with the amount of characters so it isn't going to work!

Can i do a loop to copy Column Bi then Ci, then Di and apply formatting? Really struggling to find anything useful on google!