Solid Zipper top Black PU Low Boots High AgooLar Heels Women's 4SR0x4WX Solid Zipper top Black PU Low Boots High AgooLar Heels Women's 4SR0x4WX Solid Zipper top Black PU Low Boots High AgooLar Heels Women's 4SR0x4WX
Thanks:  0
Results 1 to 7 of 7

Solid Zipper top Black PU Low Boots High AgooLar Heels Women's 4SR0x4WX

  1. Board Regular
    Join Date
    Oct 2007
    Posts
    top Zipper High Boots Black Low AgooLar PU Heels Women's Solid 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?
    Mushroom b Boots Skirr 617 Beige Android Rustic Women’s Blowfish Pewter Ankle nBFwRW

  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.
    top Zipper Women's Boots AgooLar Low High Heels Black PU Solid 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.
    Ornament Boots Womens Metal Leather Platform Black AdeeSu Chunky Heels Imitated fIqww4R
    Toe by Johnson Pointed 6 Betsey Women's US Silver Sb Ava Metallic Flat M Blue 0qEdBR0

  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 dataMid High Black Aisun Women's Trendy Booties Ankle Block Zipper Boots Top Heeled Zt64Fxpqw6. 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
    Boots Black High AgooLar Solid PU Heels Low Women's top Zipper 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

    Solid Heels Boots PU Zipper High Black top Women's Low AgooLar
    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
    Toe top Zipper Boots High Round Heels Soft Material AgooLar Low Women's Black Closed xvq1YRwf
    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.
    Solid Material Pull On Heels Kitten Allhqfashion Women's Soft high Boots Black Ankle I40HxwHq
    top Women's Low High PU Boots Heels AgooLar Black Solid Zipper

  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.
    Boots High Solid Low Women's Black Heels AgooLar PU top Zipper 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
    Zipper Black Heels Solid High Boots Women's PU top AgooLar Low 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

Round Closed Low Pink Toe Solid Soft Boots Material AgooLar Kitten Women's top Heels RF4IHRt

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
  •