Data Validation

[Suzanne B]

Using Excel 2002
I am using the data validation feature and have created a
list with dropdown selected. The list that I created has
specfic formatting to them, ie color, bold type. Is
there a way in the validation that when I select from the
dropdown menu it will carryover those formatting
features? I can't really use conditional formatting
since their is a limit of 3 and I have about 7 items that
are all color coded different. Any suggestions on how to
accomplish this?

Thanks
Suzanne

 

[Debra Dalgleish]

You could use the Change event to copy list cell and paste the format
onto the cell with data validation, as in the sample code at the end of
this message.

To add this code to the worksheet:
Right-click on the sheet tab, and choose View Code.
Copy the code, and paste it onto the code module.
Change the sheet names to match your workbook.

'===============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsL As Worksheet
Dim wsD As Worksheet
Dim rngDV As Range
Dim i As Integer
Set wsL = Sheets("Lists")
Set wsD = Sheets("DataEntry")
Application.EnableEvents = False
On Error Resume Next

Set rngDV = wsD.Cells _
.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If rngDV Is Nothing Then Exit Sub
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
On Error GoTo errHandler
i = Application.WorksheetFunction _
.Match(Target.Value, wsL.Range("ProductList"), 0)
wsL.Cells(i, 1).Copy
Target.PasteSpecial xlPasteFormats
End If

errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
'===============================