View Full Version : Change part of the text colour in VLOOKUP.
rsdjimbie
May 2, 2011, 11:18 PM
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.
rsdjimbie
May 3, 2011, 03:18 AM
Example.
JBeaucaire
May 4, 2011, 05:15 PM
This is not possible. Partial string colorization is only possible when the cell has no formula in it.
JBeaucaire
May 4, 2011, 05:48 PM
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
rsdjimbie
May 4, 2011, 11:29 PM
Ok, thank you for having a look.
rsdjimbie
May 5, 2011, 09:58 AM
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.
JBeaucaire
May 5, 2011, 11:29 AM
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.