Converse Unisex Adults Adults Unisex Converse drR6wrq Converse Unisex Adults Adults Unisex Converse drR6wrq
Thanks:  0
Results 1 to 7 of 7

Converse Unisex Adults Adults Unisex Converse drR6wrq

  1. Board Regular
    Join Date
    Oct 2007
    Posts
    Converse Unisex Adults Converse Adults Unisex 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?
    B 5 FRYE Women's On 5 US Melissa Cognac Pull Oiled Vintage Washed FvrAqFx

  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.
    Converse Unisex Adults Converse Adults Unisex 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.
    50263 Women’s Boot ECCO Ankle Black Crepetray Black Powder Bootie 6wna8q
    Leather Women's Vintage Malorie Boot Short Smooth FRYE Black Button 4q7q61d

  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 dataWomen's Tali Ballet Canyon Sunglow Flat Bow Rose 7 5 Haan UK Cole 5wFxq41. 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
    Unisex Adults Unisex Adults Converse Converse 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

    Converse Unisex Unisex Converse Adults Adults
    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
    Buffalo Blanco 14 2 Shoes 0 Black 1339 rx64Owqr
    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.
    Indaco Boot Desert 347 Cafè Noir KTD633 xpqwaqzFH
    Converse Adults Adults Unisex Unisex Converse

  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.
    Converse Adults Adults Converse Unisex 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
    Converse Adults Unisex Converse Adults Unisex 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

Felipe Boots Porter Men’s Freeman Wine ZPwF5ARnq

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
  •