T4191_Highlight Half First Word By INDEX Formula Characters

https://excel-egy.com/forum/t4191
---------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim txt As String, startChar As Integer, endChar As Integer, lr As Long

    If Target.Address = "$K$2" Then
        Application.ScreenUpdating = False
            lr = Cells(Rows.Count, 1).End(xlUp).Row

            With Range("L2")
                .Formula = "=INDEX($B$2:$B$" & lr & "&""-""&$C$2:$C$" & lr & "&""-""&$D$2:$D$" & lr & ",MATCH(K2,$A$2:$A$" & lr & ",0))"
                .Value = .Value
                If IsError(.Value) Then .Value = "No Match": GoTo Skipper

                txt = Split(.Value, "-")(0)
                .Characters(1, Len(.Value)).Font.Color = vbBlack
                startChar = Application.RoundDown(Len(txt) / 2, 0) + 1
                endChar = Len(txt) - startChar + 1
                If startChar <> 0 Then .Characters(Start:=startChar, Length:=endChar).Font.Color = RGB(255, 0, 0)
            End With
Skipper:
        Application.ScreenUpdating = True
    End If
End Sub