Event Macros Help Needed

  • Thread starter Thread starter edo
  • Start date Start date
E

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....
 
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
 
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
 
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.
 
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
 
Back
Top