Adults Converse Adults Unisex Converse Converse Unisex Adults Unisex Converse Unisex Adults Converse Owfq7TFY

You can use select case.

Note since the variable is using ucase to make it all upper case in the select case the color name needs to be all upper case.

Code:

Sub colorcell()

Dim mycolor As String


mycolor = UCase(Range("A1"))


Select Case mycolor


Case "RED"
Range("A3").Interior.Color = vbRed


Case "BLUE"
Range("A3").Interior.Color = vbBlue


Case "YELLOW"
Range("A3").Interior.Color = vbYellow


'other colors here




End Select


End Sub

I'm assuming you want to use this in a sheet change event script

Assuming you will be using this in column (5) Modify if needed
Try this:

You should put these values in a DataValidation list

Black,White,Red,Green,Blue,Yellow,Magenta,Cyan

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 12-1-17 11:00 PM EST
If Target.Column = 5 Then
Dim MyColor As String
MyColor = Target.Value
    With Target.Interior
    Select Case MyColor
        Case "Red"
            .Color = vbRed: Target.Value = ""
                Case "Red"
                    .Color = vbRed: Target.Value = ""
                Case "Black"
                    .Color = vbBlack: Target.Value = ""
                Case "White"
                    .Color = vbWhite: Target.Value = ""
                Case "Green"
                    .Color = vbGreen: Target.Value = ""
                Case "Blue"
                    .Color = vbBlue: Target.Value = ""
                Case "Yellow"
                    .Color = vbYellow: Target.Value = ""
                Case "Magenta"
                    .Color = vbMagenta: Target.Value = ""
                Case "Cyan"
                    .Color = vbCyan: Target.Value = ""
    End Select
End With
End If
End Sub

Assuming your data validation list is

Converse Adults Unisex Unisex Converse Unisex Adults Converse Adults Adults Converse Converse Unisex
Black,White,Red,Green,Blue,Yellow,Magenta,Cyan

Then this worksheet change code should work. This is using Column E (5)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrColors, vbaColors, lngIndex As Long
arrColors = Array("Black", "White", "Red", "Green", "Blue", "Yellow", "Magenta", "Cyan")
vbaColors = Array(vbBlack, vbWhite, vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, vbCyan)
If Target.Column <> 5 Then Exit Sub
If Target <> "" Then lngIndex = Application.Match(Target, arrColors, 0) - 1: Target.Interior.Color = vbaColors(lngIndex)
End Sub

Originally Posted by Scott Huish

Assuming your data validation list is

Black,White,Red,Green,Blue,Yellow,Magenta,Cyan

Then this worksheet change code should work. This is using Column E (5)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrColors, vbaColors, lngIndex As Long
arrColors = Array("Black", "White", "Red", "Green", "Blue", "Yellow", "Magenta", "Cyan")
vbaColors = Array(vbBlack, vbWhite, vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, vbCyan)
If Target.Column <> 5 Then Exit Sub
If Target <> "" Then lngIndex = Application.Match(Target, arrColors, 0) - 1: Target.Interior.Color = vbaColors(lngIndex)
End Sub

I like this array approach best. Thanks everyone.
I was hoping there was just a simple function that would turn "red" into vbred. I think I'll create a ud function for this.

Originally Posted by Roderick_E

I like this array approach best. Thanks everyone.
I was hoping there was just a simple function that would turn "red" into vbred. I think I'll create a ud function for this.

Adults Unisex Converse Unisex Converse Adults Unisex Converse Adults Converse Converse Unisex Adults Code:

Function xlcolors(color As String)
Dim arrColors, vbaColors, lngIndex As Long
arrColors = Array("Black", "White", "Red", "Green", "Blue", "Yellow", "Magenta", "Cyan")
vbaColors = Array(vbBlack, vbWhite, vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, vbCyan)
'cell colors
lngIndex = Application.Match(color, arrColors, 0) - 1
xlcolors = vbaColors(lngIndex)
End Function

Adults Unisex Unisex Converse Converse Converse Converse Adults Converse Unisex Adults Unisex Adults Code:

sub test
activecell.interior.color = xlcolors("Red")
end sub

could also do activecell.font.color = xlcolors("Red")

This is for when "Red" is a dynamic or what's in arrColors otherwise you could just use activecell.XXX = vbred