Linking shapes color to a different cell color

[rgleong]

Hi there,

Very new to excel with only a basic understanding. I have a sheet where I have numerous shapes (freeform: shape), and a table of cells in which each cell represents one of the shapes. These cells change colour and text from drop down lists. I want to be able to make the outline of the shape change colour based on what the cell value is. For example if the cell for shape 1 is colored green, and says "Normal", I want the outline of shape 1 to automatically turn green. I have looked at a few online tutorials and searched around, but struggling to make anything work. Any ideas greatly appreciated!

Thanks

[#2]

@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.

[#3]

Hi @rgleong,

You can use VBA to achieve your goal.

Here is how you can do it (it is a concept, I haven't tested it)

1. Open the Visual Basic for Applications (VBA) Editor:
- Press "ALT + F11" in Excel to open the VBA Editor.

2. Insert a new module:
- In the VBA Editor, click on "Insert" in the menu, then click "Module."

3. Paste the VBA code:
- Copy and paste the following VBA code into the module you created:

Sub ChangeShapeOutlineColor()
Dim ws As Worksheet
Dim shapeCell As Range
Dim shape As Shape
Dim cellColor As Long

'Specify the worksheet containing the shapes and the table with cell colors
Set ws = ThisWorkbook.Worksheets("Sheet1")

'Loop through each shape
For Each shape In ws.Shapes
'Get the corresponding cell based on the shape name
Set shapeCell = ws.Range("A:A").Find(shape.Name)
If Not shapeCell Is Nothing Then
'Get the color of the corresponding cell
cellColor = shapeCell.Interior.Color
'Change the shape outline color to match the cell color
shape.Line.ForeColor.RGB = cellColor
End If
Next shape
End Sub

4. Run the macro:
- Close the VBA Editor and return to your Excel sheet.
- Press "ALT + F8" to open the "Macro" dialog box.

- Select the "ChangeShapeOutlineColor" macro from the list and click "Run."

The VBA macro loops through all shapes on the specified worksheet ("Sheet1" in this example). It matches each shape's name with the content in column A to find the corresponding cell. Then, it retrieves the cell's interior color and applies it to the shape's outline color

You can check these Microsoft Learn links if you want to learn some more about VBA and macros in Excel:

Getting started with VBA in Office | Microsoft Learn

Automate tasks with the Macro Recorder - Microsoft Support

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic