How do you format data that is part of a drop down menu?

  • Thread starter Thread starter Guest
  • Start date Start date
You can change the color after the item has been selected.

Either through CF or event code.

In 2007 you can have 64 CF rules in a cell.

In 2003 only 3 rules for a cell.

Event code could look like this. Assumes A1 is DV dropdown.

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("C", "D", "G", "H", "K", "L", "O", "S", "C", "X") 'your items
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)
With Target
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(r.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
.Font.ColorIndex = icolor
End If
End With
End Sub

If you have many items in your list you may want to employ a lookup table
for vals and nums arrays.


Gord
 
Back
Top