Vb colors as strings

[Roderick_E]

I have a dropdown list in a sheet from which the user will select a color (the 7 constants) red, cyan...
In my code I wanted to simply put "vb" before it something like:

xcolor = "vb" & user-selected-color
cells(x,y).interior.color = xcolor

But it fails to produce the color. Any ideas? Is there something that evaluates a string "vbred" as vbred?

 

[Scott T]

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.

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

 

[My Aswer Is This]

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

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 12-1-17 11:00 PM EST
If Target.Column = [COLOR=#ff0000]5[/COLOR] 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

 

[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)

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

 

[Roderick_E]

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)

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.

 

[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.

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

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

 

[Scott Huish]

I would add an On Error Resume Next to that:

Function xlcolors(color As String)
On Error Resume Next
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

It will return the first color in the Array if an improper color is passed and not error out:

Sub test()
ActiveCell.Font.color = xlcolors("Orange")
End Sub