Need Help Changing the Color of an Object in Sheet1, from a cell in Sheet2 using VBA!

[Spartan920]

Before I get started, I know this is a tall order...haha. I also know that this type of question has been answered and revisited time and time again. I found a lot of useful posts, but would really like something specific to what I'm doing.

I have an Oval (Oval 1) in Sheet1, and I'd like to change it's color from cell A1 in Sheet2 using VBA.
The colors are: Red- (255, 0, 0)
Yellow- (255, 255, 109)
Green- (41, 247, 46)
and Grey- (127, 127, 127)

I'd like these colors to Fill the Oval object. I'm not too worried about the line color at this point.

Can anyone help with the coding? I'm lost.

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] Oval1_Color()
    [color=darkblue]With[/color] Sheets("Sheet1").Shapes("Oval 1").Fill.ForeColor
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] U[color=darkblue]Case[/color](Sheets("Sheet2").Range("A1").Value)
            [color=darkblue]Case[/color] "RED": .RGB = vbRed
            [color=darkblue]Case[/color] "YELLOW": .RGB = vbYellow
            [color=darkblue]Case[/color] "GREEN": .RGB = RGB(41, 247, 46)
            Case "GREY": .RGB = RGB(127, 127, 127)
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Spartan920]

I think you're on to something AlphaFrog. This is the first time I entered a code and didn't get an error message. But when I went to Sheet2/Cell A1 to enter one of the designated colors, the oval on sheet1 didn't change colors.

Any ideas?

 

[AlphaFrog]

Did you run the macro after you entered a color in A1?

Did you expect it to "automatically" change colors after you enter a color? That wasn't part of your description.

 

[Spartan920]

Sorry about that AlphaFrog. Once again it was user error, haha. Your code worked to perfection. I didn't enter the color first and run macro afterwards. Not to take up too much of your time, but is there in fact a way to do it automatically like you mentioned in your previous post?

If not, the code you provided is just fine. Thanks again.

 

[AlphaFrog]

Right-click on the Sheet2 tab
Select View Code from the pop-up context menu
Paste the code below in the worksheet's code module

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]With[/COLOR] Sheets("Sheet1").Shapes("Oval 1").Fill.ForeColor
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] U[COLOR=darkblue]Case[/COLOR](Target.Value)
                [COLOR=darkblue]Case[/COLOR] "RED": .RGB = vbRed
                [COLOR=darkblue]Case[/COLOR] "YELLOW": .RGB = vbYellow
                [COLOR=darkblue]Case[/COLOR] "GREEN": .RGB = RGB(41, 247, 46)
                Case "GREY": .RGB = RGB(127, 127, 127)
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[Spartan920]

WOW. Simply Amazing. You sir, are worth your weight in gold.

Thanks again. Have a good one.