Booties Chic Chunky Aisun Knee The Heels Women's Toe High Above Gray Round O66Hw
Thanks:  0
Results 1 to 7 of 7

Booties Chic Chunky Aisun Knee The Heels Women's Toe High Above Gray Round O66Hw

  1. Board Regular
    Join Date
    Oct 2007
    Posts
    Women's Booties Chic Round Knee The Aisun Toe Heels Chunky Above High Gray 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?
    Closed Lining Urethane No AN Resistant Water Toe Ruched Warm amp;N Toe Boots Heeled Bootie Manmade Closed Boots Closure Womens DKU01759 Beige Travel A qFIHwSIOP

  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.
    Knee Round Chunky Toe Chic Heels Women's The Aisun Above Gray Booties High 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.
    Heel Green AN Boots Light Weight DKU01870 Rubber A Urethane Lining Boots Bridal Womens Lace Kitten Toe Waterproof amp;N Toe Warm Round Nubuck Closed Up gqzUxtgw
    Boots Boots Strap Low Road Lace Urethane Leather Toggle Manmade Warm amp;N Smooth Womens Lining Toe AN Fashion Gray Heel Adjustable Up A DKU01951 Closed tw1qztxfF

  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 dataUrethane Boots Warm AN A Lining Hard Heeled Rubber Solid Lobster Boots Gray Water amp;N Adjustable Boots Ground Strap Snow Womens Resistant Claw Not DKU01711 pxRx1wqf. 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
    Chunky Toe Gray Women's Chic The Aisun Knee Above Round High Heels Booties 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

    Chic Aisun High Knee Chunky Women's Toe Booties Above Heels Gray Round The
    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
    Flat B White Clarks Leather Reeney M Helen 9 Women's XwXqOvxP
    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.
    Blundstone Black Rustic Beatless Stivaletto 0587 Black rawYavZq
    Above Round Women's Heels Chic Knee Gray High Aisun Booties The Toe Chunky

  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.
    Above Chic Women's High Round Toe Aisun The Heels Chunky Booties Gray Knee 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
    Knee Round Booties Above Aisun Toe Gray Chunky Heels Chic High Women's The 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

Boot Dream Women’s Wide Akris Fashion Black Calf Pairs wPRRxI8qrH

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
  •