Linking shapes color to a different cell color

rgleong 

To achieve the desired functionality where the outline color of a shape changes based on the corresponding cell's value and color in Excel, you can use VBA (Visual Basic for Applications) to create a macro. The following steps will guide you through the process:

Step 1: Assign Names to Shapes and Cells

1. Select each shape, go to the "Name Box" (the box beside the formula bar), and give each shape a unique name (e.g., "Shape1", "Shape2", etc.).
2. In the table, assign unique names to the cells corresponding to each shape. For example, if you have "Shape1" in cell A2, name cell A2 as "Shape1_ColoredCell".

Step 2: Create a VBA Macro

1. Press "ALT" + "F11" to open the Visual Basic for Applications (VBA) editor.
2. In the VBA editor, click "Insert" in the top menu, then choose "Module" to insert a new module.
3. Copy and paste the following VBA code into the new module:

vba code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim shp As Shape
    
    On Error Resume Next
    
    If Target.Count = 1 Then
        ' Check if the changed cell has a shape associated with it
        Set shp = Me.Shapes.Item(Target.Value & "_ColoredCell")
        
        ' If the shape is found, update its outline color
        If Not shp Is Nothing Then
            Select Case Target.Value
                Case "Normal"
                    shp.Line.ForeColor.RGB = RGB(0, 255, 0) ' Green
                Case "Warning"
                    shp.Line.ForeColor.RGB = RGB(255, 255, 0) ' Yellow
                Case "Error"
                    shp.Line.ForeColor.RGB = RGB(255, 0, 0) ' Red
                Case Else
                    ' Set a default color if the cell value doesn't match any case
                    shp.Line.ForeColor.RGB = RGB(0, 0, 0) ' Black
            End Select
        End If
    End If
End Sub

Step 3: Save and Close the VBA Editor Save the workbook as a macro-enabled workbook (.xlsm) to retain the VBA code.

Now, when you change the value in the cells corresponding to each shape, the outline color of the corresponding shape should change based on the cell's value. The code includes cases for "Normal", "Warning", and "Error", but you can customize it according to your specific needs.

Please note that this code assumes you have already set up the shapes and corresponding cells as described in Step 1. Additionally, the code should be placed in the worksheet module where you have the shapes and cells, not in a standard module. The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.