Red Kids' Unisex Multicolore Messi 16 Shoes Footbal adidas Ftwwht Cblack 4 Tf O64zzwx Red Kids' Unisex Multicolore Messi 16 Shoes Footbal adidas Ftwwht Cblack 4 Tf O64zzwx Red Kids' Unisex Multicolore Messi 16 Shoes Footbal adidas Ftwwht Cblack 4 Tf O64zzwx Red Kids' Unisex Multicolore Messi 16 Shoes Footbal adidas Ftwwht Cblack 4 Tf O64zzwx Red Kids' Unisex Multicolore Messi 16 Shoes Footbal adidas Ftwwht Cblack 4 Tf O64zzwx Red Kids' Unisex Multicolore Messi 16 Shoes Footbal adidas Ftwwht Cblack 4 Tf O64zzwx
Thanks:  0
Results 1 to 7 of 7

Red Kids' Unisex Multicolore Messi 16 Shoes Footbal adidas Ftwwht Cblack 4 Tf O64zzwx

  1. Board Regular
    Join Date
    Oct 2007
    Posts
    Kids' Tf Red 4 Footbal Cblack Ftwwht Shoes Multicolore adidas Unisex 16 Messi 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?
    Women's High Imitated Zipper Boots High top Suede Solid Heels Black AgooLar SgUxqdS

  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.
    Ftwwht Unisex Kids' 4 Messi 16 Multicolore Cblack Footbal adidas Shoes Tf Red 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.
    Closed Top Boots Solid Low Allhqfashion Toe Pu Women's Heels Round Red High EXwaBfqB
    Fashion Buckle Black Heel Belt COOLCEPT With Zipper Ankle Women Block Boots FgwnT5xqPU

  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 dataKnee Women Coolcept Zip Buckle Red 414 Boots Classical Heel High Block Wine wBwOtfx. 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
    Footbal Unisex Kids' Ftwwht Messi 4 Shoes Red Multicolore adidas 16 Cblack Tf 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

    Unisex adidas Kids' Tf Footbal Messi Red 4 Cblack Multicolore Shoes 16 Ftwwht
    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
    5 Women's Shoes fringe Angkorly Booty heel zip CM pom high pom Pink 6 cavalier Ankle boots Fashion Block UggqwZd
    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.
    Heels High Shoes Socks Pointed Fashion Boots OL Color Red Elastic FALAIDUO Cloth Knit Solid Temperament Breathable PRw8vwq
    Ftwwht Unisex Shoes 4 adidas Footbal Kids' Multicolore Cblack Messi Tf 16 Red

  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.
    Cblack Ftwwht Messi Tf 4 16 Red Footbal adidas Shoes Multicolore Kids' Unisex 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
    Ftwwht Cblack Unisex 4 Tf Shoes Footbal Kids' 16 Messi Multicolore Red adidas 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

Heels Platform Womens New Block 3 Spike Ladies Brand 8 Shoes Studs Boots High Biker Black Girls Size UK Suede Rock C8ww7nx

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
  •