Select data from a list with different colors

  • Thread starter Thread starter yllar17
  • Start date Start date
Y

yllar17

Ive been racking my brains out on this problem, but cant' find a solution..

I have a list of 16 items (row a50 to a66), each line is a different text
value, next to that list, is another list in b50 to b66, those are formatted
in different colors.

what I need is when a user selects a value from the drop down list, the
color of the selection corresponds to what is selected..

for example

1 blue
2 orange
3 pink

and so on..

the drop down list cell is in a1, when the user selects a item in the list
in cell a1, the color specified should be changed in that cell, if they
select 2 then the cell would change to orange..if they selected 1 then the
cell would change to blue and so on..

basically I need to color code the cell depending on the selection, and
also, allow the user to reformat cells b50 to b66 to suit their own choices
in color..

i thought about using conditional formatting, but 3 choices is not enough,
and also, that would make it difficult for the user to change what color
they wanted to display

help....
 
You could use the Worksheet_Change event to copy the format from column
B, and apply it to cell A1. This assumes the data validation list in
cell A1 is from cells A50:A66.

The following code is pasted on the module for the sheet that contains
the lists. Right-click the sheet tab, and choose View Code. Paste the
code where the cursor is flashing.

'======================
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim i As Integer
i = 10
If Target.Address = "$A$1" Then
Application.EnableEvents = False
With ActiveSheet
i = Application.Match(.Range("A1").Value, .Range("A50:A66"), 0)
.Range("B50").Offset(i - 1, 0).Copy
End With
Target.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Else
End If
Application.EnableEvents = True
End Sub
'===========================
 
Back
Top