Drop Down list

[aaaaa]

I've created a list of 10 names. Each name is in a different font and
color.

These 10 names make up the possible selections for a drop down list. When I
select a name from the list and excel enters it into the cell, the font
color and type that was originally given to the it when the list was created
no longer follows the selection.

Is there a way to have the correct font color and size follow the selection
based upon what name is picked.

 

[Martin Fishlock]

Hi,

Try this little bit of code. It is worksheet code so it needs to be placed
in the code for the worksheet where the datalist is.

A word of warning, it breaks down if the cell is cleared with spaces.

May somebody could comment on a solution. The error occurs when an error
condition occurs in the cell - ie you enter rubbish it can't match it so the
datavalidiation kicks (cancel ok) but retry causes the problem you clear the
bad entry and then the application.enableevents =false causes an error.

The two changes you need to make are the const refences.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Const cszCell As String = "D1"
Const cszTable As String = "A1:A10"
Dim idx As Variant

If Me.Range(cszCell) = "" Or _
Trim(Me.Range(cszCell)) = " " Then Exit Sub

Application.EnableEvents = False
On Error GoTo error_pickup

If Not (Intersect(Target, Me.Range(cszCell)) _
Is Nothing) Then
idx = Application.WorksheetFunction.Match( _
Me.Range(cszCell), Me.Range(cszTable), 0)
If IsNumeric(idx) Then
Me.Range(cszTable).Cells(1, 1).Offset( _
idx - 1, 0).Copy
Me.Range(cszCell).PasteSpecial xlPasteFormats
End If
End If
error_pickup:
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub