Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Change part of the text colour in VLOOKUP. (https://www.askmehelpdesk.com/showthread.php?t=574147)

  • May 2, 2011, 11:18 PM
    rsdjimbie
    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.
  • May 3, 2011, 03:18 AM
    rsdjimbie
    1 Attachment(s)
    Example.
  • May 4, 2011, 05:15 PM
    JBeaucaire

    This is not possible. Partial string colorization is only possible when the cell has no formula in it.
  • May 4, 2011, 05:48 PM
    JBeaucaire

    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

  • May 4, 2011, 11:29 PM
    rsdjimbie
    Ok, thank you for having a look.
  • May 5, 2011, 09:58 AM
    rsdjimbie
    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.
  • May 5, 2011, 11:29 AM
    JBeaucaire

    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.

  • All times are GMT -7. The time now is 10:59 PM.