Colour shapes when certain criteria is met on different excel tab

[Twan1980]

Hello,

I'm working on an excel and have two tabs:

Tab 1: in tab one I've added a picture (total ownership with all differtent owners of a plot of land) and on top of this picture i've drawn freeform (shapes) corresponding with the different ownership positions on this entire plot (let's say 40 different shapes). In the shapes i've added a textbox with the cadastral property number for each property.

Tab 2: In the second tab i've got the complete list of all cadastral property numbers, name of owner, price for which we want to obtain different cadastral properties etc. If a certain criteria is met, let's say in Kolom "H" is have the criteria "on" or "off" and the criteria is on for property number 530, I want the corresponding cadastral property in the shapes on tab 1 to get a different colour.

Is this possible and yes, how do I do this.

Hope you can help. PS: i;m working with excel 2003 and 2007.

Twan!

 

[Warship]

With the Shape names in Column A and the toggle in Column H on Sheet2.
Place this code in the Sheet2 module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("H")) Is Nothing Then
        If UCase(Target) = "ON" Then
            Sheets("Sheet1").Shapes(Cells(Target.Row, "A")).Fill.ForeColor.SchemeColor = 2
        Else
            Sheets("Sheet1").Shapes(Cells(Target.Row, "A")).Fill.ForeColor.SchemeColor = 1
        End If
    End If
End Sub