Updating shape colors when opening workbook.

[xe-dingo]

Hi,

I do not have much experience in VBA, but I assume this should be relatively easy.

I have multiple shapes which change color depending on the value from certain cells.
The values within the cells are linked to an external workbook.
The values within the cells update when I open the shape workbook, but not the actual shape colors.

- How can I write the code so that the shape colors update automatically when workbook opens (or opens sheet for that matter).

Following code is written in sheet1 code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim ShapeName As String
Dim SHP As Shape

ShapeName = "A"

Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("R101")
Set SHP = Rng.Parent.Shapes(ShapeName)

If Rng.Value = "OPEN" Then
SHP.Fill.ForeColor.RGB = RGB(43, 170, 26) ' green
End If
If Rng.Value = "CLOSED" Then
SHP.Fill.ForeColor.RGB = RGB(255, 0, 0) ' red
End If
If Rng.Value = "N/A" Then
SHP.Fill.ForeColor.RGB = RGB(255, 255, 255) ' white
End If

ShapeName = "B"

Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("R228")
Set SHP = Rng.Parent.Shapes(ShapeName)

If Rng.Value = "OPEN" Then
SHP.Fill.ForeColor.RGB = RGB(43, 170, 26) ' green
End If
If Rng.Value = "CLOSED" Then
SHP.Fill.ForeColor.RGB = RGB(255, 0, 0) ' red
End If
If Rng.Value = "N/A" Then
SHP.Fill.ForeColor.RGB = RGB(255, 255, 255) ' white
End If

End Sub