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:


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

Sub DelNAErrorsOnEmbSheet()
On Error Resume Next

Sheets("Emb").Cells.SpecialCells(xlCellTypeFormulas, 16).ClearContents

End Sub


Module7


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:

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.