Example, "DS1 WB, Left+Right Pkt Lining" code is recorded on a sheet and I use VLOOKUP to "transfer" to another sheet, however, in doing so I need to change the colour of certain text in the string, for example, when "DS1 WB, Left+Right Pkt Lining" is read by VLOOKUP to the second sheet, do I want the "DS1" only to be changed to a different colour. Tried many ideas but no joy.

Example.

This is not possible. Partial string colorization is only possible when the cell has no formula in it.

Some code streamlining suggestion, Module54:

Code:

---

Dim ws As Worksheet
For Each ws In Sheets(Array("Prod A1", "Prod A2", "Prod B", "Prod C1", "Prod C2", "Prod D"))
    ws.Columns("Y:Y").Delete Shift:=xlToLeft
    ws.Range("H1:S2").Value = ws.Range("H1:S2").Value
    ws.Range("E4").Value = ws.Range("E4").Value
    With ws.Rows("5:100")
        .Orientation = 0
        .AddIndent = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .FormatConditions.Delete
        .Value = .Value
    End With
Next ws

---

Module8

Code:

---

Sub DelNAErrorsOnEmbSheet()
On Error Resume Next
   
    Sheets("Emb").Cells.SpecialCells(xlCellTypeFormulas, 16).ClearContents
   
End Sub

---

Module7

Code:

---

Sub Unprotect()
Dim ws As Worksheet
For Each ws In Sheets(Array("Emb", "Prod A1", "Prod A2", "Prod B", "Prod C1", "Prod C2", "Prod D"))
    ws.Unprotect
Next ws
 
End Sub

Sub Protect()
Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets(Array("Emb", "Prod A1", "Prod A2", "Prod B", "Prod C1", "Prod C2", "Prod D"))
    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        AllowFormattingCells:=True, AllowInsertingRows:=True, _
        AllowDeletingRows:=True
Next ws

End Sub

---

Ok, thank you for having a look.

I just thought of something. What if we convert the formulas on the sheet, all of them, to there values at the start of the macro and then do the string colorization? Could work. Because I don't have to save the sheet, I will close the appliaction and start with a unchanged sheet.

Yes, you can do that.

This code will convert a cells to its value then color the first word:

Code:

---

    With Range("B10")
        .Value = .Value
        .Characters(Start:=1, Length:=InStr(.Value, " ") - 1).Font.ColorIndex = 3
    End With

---

You could adapt that to run on your column B cells of interest. As for your column L values, there's no apparent logic I could apply to teach a macro which word(s) to color. You'll have to devise a logical explanation that an unthinking macro can use to determine the correct word(s) each time.