Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macros Help Needed
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.... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macros Help Needed
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 -- Gary''s Student - gsnu200835 "edo" wrote: 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.... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macros Help Needed
where is the worksheet code area?
thanks btw for your response! "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200835 "edo" wrote: 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.... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macros Help Needed
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" wrote: 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.... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macros Help Needed
Thanks Gary. I figured out where to put the code into the worksheet. What
happens next? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200835 "edo" wrote: 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.... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macros Help Needed
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. -- Gary''s Student - gsnu200835 "edo" wrote: Thanks Gary. I figured out where to put the code into the worksheet. What happens next? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200835 "edo" wrote: 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.... |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macros Help Needed
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 "edo" wrote in message ... 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
event macros vs copy/paste | Excel Worksheet Functions | |||
Working with Macros - Help needed!!! | Excel Discussion (Misc queries) | |||
Event Macro adjustment needed - need to change font color also | Excel Worksheet Functions | |||
formula needed to track dates event happened | Excel Worksheet Functions | |||
event macros - xl 2003 | Excel Worksheet Functions |