Event Macros Help Needed

[edo]

Is it possible to create a formula to have items in a dropdown menu in a cell
automatically change to a certain color when selected? There are more than 3
colors so Conditioning Format will not work for this....

 

[Gary''s Student]

Put something like this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a1 = Range("A1")
If Intersect(t, a1) Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case t.Value
Case "dog"
i = 3
Case "cat"
i = 5
Case "bird"
i = 10
Case "fish"
i = 6
End Select
t.Interior.ColorIndex = i
Application.EnableEvents = True
End Sub

 

[edo]

where is the worksheet code area?
thanks btw for your response!

 

[Shane Devenshire]

Hi,

You can record a macro to change the color of a cell so you know the code
for the colors you want. Just change the color of a single cell to each of
the colors you want and then create something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target > 100 Then
Target.Interior.ColorIndex = 46
ElseIf Target = "Stanford" Then
Target.Interior.ColorIndex = 8
End If
End If
End Sub

The is entered in the Sheet object for the sheet where your drop down list is.

You might also look at the Select Case instead of IF.

If this helps, please click the Yes button,

Cheers,
Shane Devenshire

 

[edo]

Thanks Gary. I figured out where to put the code into the worksheet. What
happens next?

 

[Gary''s Student]

The macro looks at cell A1. Assuming that cell A1 has a data validation
drop-down, when the user selects one of the options, the cell should change
color to match.

You can modify the code to look at a different cell and use a different set
of selections.

 

[Harald Staff]

Rightclick sheet tab, "view code", paste this in and edit address and
values:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False, xlA1) = "C1" Then
Select Case Target.Value
Case "a"
Target.Interior.ColorIndex = 8
Case "b"
Target.Interior.ColorIndex = 39
Case "c"
Target.Interior.ColorIndex = 10
Case "d"
Target.Interior.ColorIndex = 14
'and so on
Case Else
End Select
End If
End Sub

HTH. Best wishes Harald