Changing colour of AutoShape

[greengo]

My problem is that depending on a value in a cell on my sheet the colour of and AutoShape will be changed. When I record the macro it seems to record all the correct functions. But when I try to run the code nothing happens to the shapes. Anyone got any idea’s

 

[acron]

Try the following code which should change the color of an AutoShape named "MyShape", based on the value in A1.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    With ActiveSheet.Shapes("MyShape").Fill.ForeColor
        Select Case [A1].Value
            Case 1
                .SchemeColor = 40
            Case 2
                .SchemeColor = 12
            Case 3
                .SchemeColor = 10
            Case Else
                .SchemeColor = 9
        End Select
    End With
Application.EnableEvents = True
End Sub
[code]
Enter the code by right clicking on the Sheet Tab, selectiong View Code and then enter or paste the code. You can adapt range and values to your needs.

You can name an AutoShape by yselecting it, and in the Name Box (just above the A1 Cell) enter the name you wish to apply.

A.C.

 

[greengo]

thanks for the reply, got it sorted anyway, it seemed that VBA doesn’t like you saying .Select("AutoShape" & 1) you need to say .Select("AutoShape " & 1). weird one I know. thanks for the quick response anyway

S.G