CODEWITHSUNDEEP
excelvbargb
Monojoll
Monojoll

Reputation: 55

changing shape color with variable

I'm having problems changing the color of a shape. I have this color data base 

BK = RGB(0, 0, 0) 'Black
BN = RGB(140, 78, 2) 'Brown
RD = RGB(239, 2, 2) 'Red
OG = RGB(255, 192, 0) 'Orange
YE = RGB(255, 255, 0) 'Yellow
GN = RGB(0, 176, 80) 'Green
BU = RGB(0, 176, 240) 'Blue
VT = RGB(112, 48, 160) 'Violet
GY = RGB(113, 113, 113) 'Grey
WH = RGB(255, 255, 255) 'White

and this piece of code that reads some data in a cell and returns the first two string members. 

color_value1 = .Range("D9").Value
wire_color1 = Mid(color_value1, 1, 2)

wire_color1 returns text like WH, GY, VT, etc. and i want to actively change the color of a shape acording to what wire_color1 returns. The problem is that this: 

m.Shapes("wire_1").Fill.ForeColor.RGB = wire_color1

doesn't work and I don't know what else to do

thank you !

Upvotes: 0

Views: 542

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

You cannot refer to a variable using a string which represents its name. You'll need to build a function (maybe using Select Case) which maps the string (eg) "BK" to an RGB value.

m.Shapes("wire_1").Fill.ForeColor.RGB = MyRGB(wire_color1)

Function:

Function MyRGB(clr as String)

    Select Case clr
        Case "BK": MyRGB = RGB(0, 0, 0) 'Black
        Case "BN": MyRGB  = RGB(140, 78, 2) 'Brown
        Case "RD": MyRGB  = RGB(239, 2, 2) 'Red
        'etc...
    End Select

End function

Upvotes: 1

Related Questions