Keep Words Bold in Data Validation

Question

I am attempting to create drop-down lists using data validation, and my formatting is lost when I create these tables. I want the list to contain bold words. The only other questions regarding this have to do with conditional formatting and keeping colors. Does anyone know how to maintain bold words in their drop-down list? Thanks.

Answer 1

You could use event code to show your DV choice in a bold color.

Don't know how practical it would be.

Private Sub Worksheet_Change(ByVal Target As Range)

Set r = Range("A1")

If Intersect(Target, r) Is Nothing Then

    Exit Sub

End If

vals = Array("Cat", "Dog", "Goat", "Hippo", "Koala", "Lynx", "Oryx", "Stag", "Yak")

nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 15)

For Each rr In r

    icolor = 0

    For i = LBound(vals) To UBound(vals)

        If rr.Value = vals(i) Then

            icolor = nums(i)

        End If

    Next

    If icolor > 0 Then

   With rr.Font

         .ColorIndex = icolor

         .Bold = True

   End With

    End If

Next

End Sub

Gord

Answer 2

It is not possible to preserve formatting in a validation dropdown list.

Answer 3

Great idea, I ran with it and completely solved my problem! I wanted only specific characters in bold. Was able to piggy-back off your code and complete it. Below is my Worksheet Change event code (it does not include the code used to create the data validation list).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim EventLen, DateLen, PolarityLen, ExtractionLen, ExtractionPos, Divider1Len, Divider2Len, Divider3Len, Divider4Len, Divider5Len, i As Integer

    Dim Divider1, Divider2, Divider3, Divider4, Divider5 As String

    'Define the dividers/lengths of dividers, and the strings/lengths of strings

    Divider1 = "_"

    Divider2 = " "

    Divider3 = " < "

    Divider4 = " > "

    Divider5 = """"

    Divider1Len = Len(Divider1)

    Divider2Len = Len(Divider2)

    Divider3Len = Len(Divider3)

    Divider4Len = Len(Divider4)

    Divider5Len = Len(Divider5)

    EventLen = Len(ActiveWorkbook.Worksheets(1).Name)

    DateLen = Len(ActiveWorkbook.Worksheets(1).Range("B2"))

    PolarityLen = Len(ActiveWorkbook.Worksheets(1).Range("D2"))

    ExtractionLen = Len(ActiveWorkbook.Worksheets(1).Range("C2")) + 1

    ExtractionPos = InStrRev(ActiveWorkbook.Worksheets(1).Range("E2"), ActiveWorkbook.Worksheets(1).Range("C2"))

    'Defines B9 to be the cell that triggers event upon change

    Set r = Range("B9")

    If Intersect(Target, r) Is Nothing Then

        Exit Sub

    End If

    'Makes the specific string of characters bold

    With ActiveWorkbook.Worksheets(1).Range("B9").Characters(Start:=EventLen + Divider1Len + DateLen + Divider2Len + PolarityLen + Divider3Len, Length:=ExtractionLen).Font

        .FontStyle = "Bold"

    End With

End Sub

This turns the substring "EXTRACTION" bold within the larger string "EVENT_DATE POLARITY < EXTRACTION > "SENTENCE"".