|
|
|
|
Junior Member
|
|
May 2, 2011, 11:18 PM
|
|
Change part of the text colour in VLOOKUP.
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.
|
|
|
Junior Member
|
|
May 3, 2011, 03:18 AM
|
|
Example.
|
|
|
Software Expert
|
|
May 4, 2011, 05:15 PM
|
|
This is not possible. Partial string colorization is only possible when the cell has no formula in it.
|
|
|
Software Expert
|
|
May 4, 2011, 05:48 PM
|
|
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
|
|
|
Junior Member
|
|
May 4, 2011, 11:29 PM
|
|
Ok, thank you for having a look.
|
|
|
Junior Member
|
|
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.
|
|
|
Software Expert
|
|
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:
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.
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Change in skin colour
[ 2 Answers ]
Hi
I am getting married in another 6 months . I am very fair and beautiful person but my only problem is that the area around my vagina and back side is very dark.
It could be because I had put a lot of weight in between , but now I am back to normal but still the colour of skin has not...
Hair colour change
[ 5 Answers ]
I dyed me hair jet black about a year ago and I'm bored of it now and want to go brunnette again, however I can't afford to get it done in a salon, is their any home kits I could use to change my hair colour? I really hate it now and I'm desperate!
Thanks
Sarah
Hair colour change after death
[ 0 Answers ]
My grandmother died at the age of seventy - nine three years ago. When she was buried her hair was white. It is obligatory in Greece ,where I live ,that the dead should be exhumated after three years of their burial. Last Monday, 11 July, 2005 after the macabre scene of the exhumation my mother and...
View more questions
Search
|