So is there any way I can have a dropdown box with the options, "G/Y/R", and then once the user makes their selection, change the cell contents to be a circle, colored appropriately based on whether the user selected G, Y or R?
You can't do this with the standard Data Validation drop down and it can only be done using VBA code.
If you can use a VBA solution then perhaps the attached will be helpful.
In the attached workbook . . .
An active-X combobox was created using a ListFillRange in column-K.
The code targets column-A. If the activecell is in column-A, then the comboxbox is made visible and presents a choice of G/Y/R. Once the selection is made, the activecell is formatted to the wingding font using the applicable color.
Option Explicit
Private Sub ComboBox1_Change()
With ActiveCell
.Font.Name = "Wingdings"
.Value = "l"
Select Case Me.ComboBox1.Value
Case "G": ActiveCell.Font.ColorIndex = 10
Case "Y": ActiveCell.Font.ColorIndex = 6
Case "R": ActiveCell.Font.ColorIndex = 3
End Select
End With
Me.ComboBox1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then
With Me.ComboBox1
.Visible = True
.Top = ActiveCell.Top
.Left = ActiveCell.Left
End With
Else
Me.ComboBox1.Visible = False
End If
End Sub
Bookmarks