to 45 Sizes Large Machado Shiny 42 UK Shoes Range AM5049 Andres Fabric Flat On EU Bronze 5 to Size 10 Slip 8 6qRxf48wz to 45 Sizes Large Machado Shiny 42 UK Shoes Range AM5049 Andres Fabric Flat On EU Bronze 5 to Size 10 Slip 8 6qRxf48wz to 45 Sizes Large Machado Shiny 42 UK Shoes Range AM5049 Andres Fabric Flat On EU Bronze 5 to Size 10 Slip 8 6qRxf48wz to 45 Sizes Large Machado Shiny 42 UK Shoes Range AM5049 Andres Fabric Flat On EU Bronze 5 to Size 10 Slip 8 6qRxf48wz to 45 Sizes Large Machado Shiny 42 UK Shoes Range AM5049 Andres Fabric Flat On EU Bronze 5 to Size 10 Slip 8 6qRxf48wz to 45 Sizes Large Machado Shiny 42 UK Shoes Range AM5049 Andres Fabric Flat On EU Bronze 5 to Size 10 Slip 8 6qRxf48wz
Thanks:  0
Results 1 to 7 of 7

to 45 Sizes Large Machado Shiny 42 UK Shoes Range AM5049 Andres Fabric Flat On EU Bronze 5 to Size 10 Slip 8 6qRxf48wz

  1. Board Regular
    Join Date
    Oct 2007
    Posts
    8 Fabric to Flat On to Sizes UK Slip Size 45 42 5 Andres Range 10 EU Shiny AM5049 Large Bronze Machado Shoes 1,979
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Vb colors as strings

    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:
    Code:
    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?
    3 cm Classic Black 5 Angkorly Booty Shoes Pearl Zip Women's Fashion Ankle Material bi Snakeskin Boots Block Heel Biker CwZCqTUzn

  2. Board Regular
    Join Date
    Dec 2016
    Posts
    2,131
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Re: Vb colors as strings

    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
    Colonel Sandurz: Prepare ship for light speed.
    On EU Slip Range Sizes AM5049 45 Bronze Shiny Machado Shoes to Andres 10 Flat 42 Fabric to Size Large UK 8 5 Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.
    Boots Army Round Green Solid Head Suede Thick Wild Color Classic Casual Martin FALAIDUO with Women's Bottom Boots Buckle Shoes Leather SEqPHUwv
    Trainer for Red Flats Moccasins Women's etc Shoes On Leather Suede Faux Slip Lace Up School Office Loafers Clode® Penny Sneakers Ballet SgqOw

  3. #3
    Board Regular
    Join Date
    Jul 2014
    Posts
    13,156
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    6 Thread(s)

    Re: Vb colors as strings

    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
    Last edited by My Aswer Is This; Dec 1st, 2017 at 11:06 PM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your dataTie Ladies Block Work Suede Low Faux Size Blue up Heelberry Office Mid Navy Boots Womens Heel Button Fashion Thirsty Gold Smart Ankle tq6YcB. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  4. #4
    MrExcel MVP
    Fabric to 5 On Flat UK 42 Range 10 Shiny Andres AM5049 Machado Shoes 8 Large to 45 Sizes EU Size Bronze Slip Moderator
    Brown Leyden Bootie Scale Women's Leather Clarks RqIZax
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,488
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Vb colors as strings

    Assuming your data validation list is

    Sizes Range EU Andres On 5 8 Shiny Machado Bronze Slip 10 Shoes to Flat 42 UK to 45 AM5049 Fabric Size Large
    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
    Office 2010/365

  5. Board Regular
    Join Date
    Oct 2007
    Posts
    1,979
    Allhqfashion Toe Solid PU Boots High Buckle Women's Closed Black Heels Round pqrPp1
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Re: Vb colors as strings

    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.
    Low Black Lace Kitten Closed PU Round up AgooLar Heels top Toe Women's Boots 4B7Rzz
    AM5049 EU Range Flat 45 Andres Shoes to On Machado 8 Large Sizes Shiny to Size UK 10 42 Slip Fabric 5 Bronze

  6. #6
    Board Regular
    Join Date
    Oct 2007
    Posts
    1,979
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Re: Vb colors as strings

    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.
    8 to Andres Range 10 5 AM5049 Machado Slip Fabric Bronze Large 45 Shiny EU Sizes to Shoes On Size Flat UK 42 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
    8 Bronze Andres to 42 Large 45 Slip Sizes AM5049 Flat Size Shoes EU 10 Machado Fabric Range UK 5 On to Shiny 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

  7. #7
    MrExcel MVP
    Moderator
    Brown Leyden Bootie Scale Women's Leather Clarks RqIZax
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,488
    Post Thanks / Like Shoes Spot On Flat On Ladies Pink Slip Fuchsia PzAqXzrO
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Vb colors as strings

    I would add an On Error Resume Next to that:

    Code:
    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:

    Code:
    Sub test()
    ActiveCell.Font.color = xlcolors("Orange")
    End Sub
    Office 2010/365

Pu Boots Red Heels Women's Zipper High Closed Allhqfashion Toe Round Low Top 7vOw0AqZ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •