Trouble with changing autoshape colours via cell drop down options

[Zero_Eclipse]

Hi all,

I am currently attempting to build a strategic assesment report for my department using Windows XP MS Excel 2003; unfortunately the requirement is for an automated system without buying an off the shelf package and quite frankly this has caused me sleepless nights, as I have never worked with visual basic before, and teaching myself over the last few days has achieved very little.

I have a workbook with multiple worksheets, on each worksheet (2 onwards) cells C21 to C23 contain dropdown lists (created via validation and have corresponding conditional formatting (R.A.G)). The drop downs list High, Medium or Low as options and the cells will turn Red, Amber or Green accordingly. On worksheet 1 there are Traffic lights (drawn using autoshapes and one for each worksheet). Cell C21 on each sheet will link to the first bulb in a corresponding traffic light image. Cell C22 links to the second bulb and Cell C23 links to the third bulb. Each bulb needs to turn Red, Amber or Green when the corresponding cell is selected as High, Medium or Low.

I have tried pasting the below into view code on each worksheet amending as required to match the worksheet and autoshapes as required, but to no avail.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$21" Then
'Change autoshape color to red, amber or green depending upon cell value.
With WorksheetSheet1.Shapes("Autoshape 6").Fill.ForeColor
If Target.Value = "High" Then
.SchemeColor = 3
ElseIf Target.Value = "Medium" Then
.SchemeColor = 45
ElseIf Target.Value = "Low" Then
.SchemeColor = 51
End If
End With
End If

If Target.Address = "$C$22" Then
'Change autoshape color to red, amber or green depending upon cell value.
With WorksheetSheet1.Shapes("Autoshape 2").Fill.ForeColor
If Target.Value = "High" Then
.SchemeColor = 3
ElseIf Target.Value = "Medium" Then
.SchemeColor = 45
ElseIf Target.Value = "Low" Then
.SchemeColor = 51
End If
End With
End If

If Target.Address = "$C$23" Then
'Change autoshape color to red, amber or green depending upon cell value.
With WorksheetSheet1.Shapes("Autoshape 3").Fill.ForeColor
If Target.Value = "High" Then
.SchemeColor = 3
ElseIf Target.Value = "Medium" Then
.SchemeColor = 45
ElseIf Target.Value = "Low" Then
.SchemeColor = 51
End If
End With
End If

End Sub

Please can anyone help me as I really am struggling.

 

[Andrew Poulsom]

For ColorIndex 45 and 51 you should be able to use SchemeColor 52 and 58.

 

[Zero_Eclipse]

Andrew,

I'm not sure why I have just re-initialised MS Excel and I'm no longer getting the error code. I will change the color for green however as I'm using a fill effect that makes it look like black.

Many thanks for all your assistance in this issue, as it is much appreciated.

Regards

Gary

 

[Zero_Eclipse]

Andrew,

Apologies, its me again (lol). The coding works perfectly in its current state, however I have another issue when I rename the specific autoshapes (eg Autoshape 6 to OPOBJ6CQ). Does the coding need to be altered further as the following code (Worksheets("Sheet1").Shapes("OPOBJ6CQ").Fill.ForeColor) returns an error of Item could not be located. Any ideas?

 

[Zero_Eclipse]

Found this issue. It seems that if you rename autoshapes from their default that are grouped the Macro cannot locate the item as it will only recognise the group allocated number. I have since created new autoshapes and in put the code as originally created.

Many thanks for all your help.