Multiple Column Lookup Needs to combine formatting for Percentage and Text String.

I am using a variant of a code excerpt for using multiple columns validation list boxes.

The code snippet is as follows

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim RngSet1 As Range
    Dim RngSet2 As Range

Application.ScreenUpdating = False

If Target.Column = 8 Or Target.Column = 9 Then
    Set RngSet1 = Range(Range("H2"), Range("H2").End(xlDown))
    Set RngSet2 = Range(Range("J2"), Range("J2").End(xlDown))
    Call UpdateValidationListPercent(RngSet1, RngSet2)
    Worksheets("CST").Range(Range("J2"), Range("J2").End(xlDown)).Name = "P6PlanLookup"
    Target.Select
End If

Application.ScreenUpdating = True

End Sub

Sub UpdateValidationListPercent(RngSet1 As Range, RngSet2 As Range)
    
    For Each CL In RngSet1.Cells
        CL.Offset(0, 2).NumberFormat = "0%"
        CL.Offset(0, 2).Formula = "=" & (CL.Address)
        CL.Offset(0, 2).NumberFormat = "@    " & """" & CL.Offset(0, 1).Value & """"
    Next
    
End Sub

Column 8 = Column H contains a percentage. Column 9 = Column I contains a description of what the user must have completed to earn the percent complete. Column 10 = Column J stores the data from column 8 but is formatted to read as the value in column 8 followed by the description from column 9. Column 10 or J is the range to which the named range is pointed for the validation list data. So even through it is supposed to read percentage then text, it only return the percentrage to the cell using the validation list.

My intent is to make the formatting do the following:
Column 8      Column 9                 Column 10
0%            Not Started              0%    Not Started
50%           Half Way Complete        50%  Half Way Complete
98%           Almost There!!!          98%  Almost There
100%          You got it Pontiac!      100%  You got it Pontiac!

The code above works perfect if the values in column 8 are text based or general based, but when using percentages in column 8, the description text of column 9 disappears and the percentage goes back to being formatted as a decimal i.e. 15% becomes 0.15.

A second issue to be worked out later is that my data ranges that will be using these validation lists as the source for the drop downs are quite narrow column widths (They are only intended to store the percentage value!). The descriptions are quite lengthy and as such I only see a portion of the descriptions in the validation list drop down box. I have been searching for hours on the net for any way to customize the width of the validation list drop down box, but it seems the concensus is to write some VBA code to make the column housing the drop down box wider (temporarily) as the width of the drop down is based on the width of the column using it, and then reset back to the original column width after the user navigates away to some other part of the sheet. This is quite annoying to me and seems to me something that the user should be able to change via a properties dialog box. Maybe in some future version of Excel!. I have 20 or so columns that point to 20 different validation lists with an unknown number of rows of data being pulled in from multiple access databases, so trying to overlay comboboxes on the cell doing the lookup seems daunting.

Thanks.

I have come up with what should have been an obvious work around the first time out of the gate, however it does not address the fundamental question of how to combine the formatting of a percentage value followed by a text string.

I left column 8 as is. I altered column 9 to append the percent complete value at the front of the description. Then I revised the subroutine which points column 10 to take the value of column 8 but format the visible value to the value in column 9 only, rather than try to combine the values from column 8 and column 9.

Here is the revised versions.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim RngSet1 As Range
    Dim RngSet2 As Range

Application.ScreenUpdating = False

If Target.Column = 8 Or Target.Column = 9 Then
    Set RngSet1 = Range(Range("H2"), Range("H2").End(xlDown))
    Set RngSet2 = Range(Range("J2"), Range("J2").End(xlDown))
    Call UpdateValidationListPercent(RngSet1, RngSet2)
    Worksheets("CST").Range(Range("J2"), Range("J2").End(xlDown)).Name = "P6PlanLookup"
    Target.Select
End If

Application.ScreenUpdating = True

End Sub

Sub UpdateValidationListPercent(RngSet1 As Range, RngSet2 As Range)
    
    For Each CL In RngSet1.Cells
        CL.Offset(0, 2).NumberFormat = "0%"
        CL.Offset(0, 2).Formula = "=" & (CL.Address)
        CL.Offset(0, 2).NumberFormat = """" & CL.Offset(0, 1).Value & """"
    Next
    
End Sub

Now the columns look like this:

Column 8      Column 9                      Column 10
0%              0% Not Started                0%  Not Started
50%            50% Half Way Complete         50%  Half Way Complete
98%            98% Almost There!!!           98%  Almost There!!!
100%          100% You got it Pontiac!      100%  You got it Pontiac!